Free Microsoft Excel 2013 Quick Reference

VLOOKUP next highest value

I have a simple vlookup. Two column array, but when I look into my first column which is ranged ages, I do not want vlookup to find what is LT or =. I want to find the next value that is GT the specified lookup value.

Am I using the wrong function?

Thank You!

Monique


In Col D, I need a formula that will find the next highest value from Col G:Col TX. In Col E, I have a max value. All suggestions appreciated.

******** ******************** ************************************************************************>Microsoft Excel - CASH 3 ALL DRAWS 6-DIGIT SERIES.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE145F145G145E146F146G146E147F147G147=
DEFG145 11719.1184146 11215.4731147 11914.873SERIES SKIPS
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hi,
Using VBA how would I take the value the user types in a text box and find the next highest value from my list?

The requirement is:
The user may type into the text box a whole number or a fractional number. Example 11 or 11.25
I have to find a value from a list based on the number the user typed in. The list starts with 6, is always an integer and every number there after is 4 more than the last.
Example of List:
6
10
14
18

The value I want to find is the number that is the next highest number in the list. For Example if the user types 11 in the text box then I want the return to be 14. If the user types in 15.33 then I want the return to be 18 and so on

Here is my code block that I'll be inserting this into. I'll be adding more but this will set my variable.

Private Sub UserForm_initialize()

Set wa = Range("MaxSpan")

wb = Here is where I need help. Lookup me.textboxActualHeight.value in wa. wb will be the next highest value in wa. wa looks like the list I showed in the beginning of this post.

End Sub

Help!

Thanks Jody

Hi,
Im just setting up a spreadsheet for structural timber design calculations. One of the requirements for this spreadsheet is to look up a Zx value from a table. Once the Zx value is found it returns a size of timber from another column. I have managed to create the function so that it looks up the Zx value and returns the size. However, if the function cant find the exact Zx value, which will usually be the case. The next lowest Zx value is used. Is there a way i can edit this function so that it returns the next highest Zx value instead of the next lowest.

At the moment this is the code i am using.


	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Where

Q92 is the value to be looked up

E5:E172 is the column of values to be searched

C5:C172 is the column of values where the result comes from

In addition to this just to further complicate things, can it return say the next 5 highest values?

If a lookup function cant be used could you tell me another way that i can achieve this.

Im sorry if this is not too clear, if you need further information let me know.

cheers

Hi,
Im just setting up a spreadsheet for structural timber design calculations. One of the requirements for this spreadsheet is to look up a Zx value from a table. Once the Zx value is found it returns a size of timber from another column. I have managed to create the function so that it looks up the Zx value and returns the size. However, if the function cant find the exact Zx value, which will usually be the case. The next lowest Zx value is used. Is there a way i can edit this function so that it returns the next highest Zx value instead of the next lowest.

At the moment this is the code i am using.


	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Im sorry if this is not too clear, if you need further information let me know.

I am using VLOOKUP with the not_exact_match set to True, however instead of finding the next largest value that is less than value, I want to find a way of returning the next largest value that is greater than value. I have looked at using MATCH and OFFSET to try and increment the returned value by 1, but so far haven't managed to figure this out.

Can anyone suggest a way to achieve this.

Many thanks

What is the best formula or method to use Vertical Lookup to get the next higher number if it's not equal versus getting the next lower value?

I have a lookup to select a safe crane for heavy lifting. I have the weight of the load and a lookup to select a crane. However, using "TRUE" the lookup will select the nearest weight crane. For safety I need to select the "next highest" spec crane. Using FALSE needs an exact match which rarely happens. Any ideas for WSheet function or VBA code?

Thanks

BallGazer

Just cant seem to get it. I am working with data in F10:F100 that contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4). I can
not come up with a formula that will index this column and return the next
highest C number. F9 needs to display the next highest C number. I can get
this to work with the standard numbers only (1,2,3,4). Any suggestions

I have tried doing this with INDEX and MATCH but can't get my head round it.

I have 2 columns of data, containing blanks that are the result of an IF
formula.

40
100
100
200
400
600
800
1200
1600

The values will always be sorted from low to high. What I need to do is take
the max value from column B (400 in this case) and return the next highest
value from column A (600).

Grateful for any pointers on this.

Hi, Group
I have tried doing this with INDEX and MATCH but without success.
I have 2 columns of data, A and B

A B

40 40
100 50
100 70
200 115
400 365
600 40
800 80
1200 985
1600 1150

What I need to do is take
the one value from column B (365 in this case) find the closest value in A
then return the next highest value from column A (600).

Grateful for any help !!

Hi All,

I have a set of numeric values in a column that will vary between 0 and 80. Some columns may have the same number more than once.

I would like to create nominal numeric values in the next column to represent the ranking of the values in the previous column with 1= highest value, 2=next highest value etc.

It would be great to adjust for ties, i.e., two top values of 28 would result in two "1s" no "2s," rank would continue with "3"

I know I can do this with a sort but we will have hundreds of these!

I have attached a sample file.

Thanks.

Hi All .
Is there any way we can make Vlookup select the next value that is highest
or equal to in a cell instead of the nearest lower value
--
Jack

VLOOKUP returns the value corresponding to the next SMALLEST value in the first column of an array. I want to get the next LARGEST value, instead.

For example, if I have 1,2,4,5 in myRange, VLOOKUP(3,myRange,1) returns 2; I want it to return 4.

Is there any way to do this with Excel's built-in functions? I've written a macro to do it using the MATCH function(which does allow you to specify smaller or larger), but I think it's slower than it needs to be.

Thanks for any assistance!

This is crossposted from MrExcel.com - http://www.mrexcel.com/forum/showthread.php?t=584583.

Is there a way, using VBA, to return the value of a particular row label that is next highest in the hierarchy?

For example, if I have a PivotTable with the following structure (and for simplicity purposes, we'll say the PivotField names are "Date", "Domain Name", "Site", and "Keyword"):

	VB:
	
[Date] 
---[Domain Name1] 
------[Site1] 
---------[Keyword1] 
---------[Keyword2] 
---------[Keyword3] 
------[Site2] 
---------[Keyword4] 
---------[Keyword5] 
-----[Domain Name2] 
------[Site3] 
---------[Keyword6] 
------[Site4] 
---------[Keyword7] 
---------[Keyword8] 
---------[Keyword9] 
---------[Keyword10] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I want to be able to have VBA look at, say, [Keyword8] and be able to return [Site4] and [Domain Name2]; or look at [Keyword2] and be able to return [Site1] and [Domain Name1].

If any additional information is required, please let me know. And as always, any help is much appreciated!

~Keith

I have a worksheet with a bunch of pull down menus where data can be selected
and then inputed into a formula. I then want to take the formula and
automatically have a part# selected. When there isn't an exact match VLOOKUP
always selects the next lowest value, I want it to select the next largest.
This is the formula I'm using right now.

=VLOOKUP(B18,sixty,2) With B18 being the formula result, and sixty the data
range on the next worksheet.

I would like to look-up a value in a table. and in case of not an exact match
select the next higher value.
Vlookup rounds of downwards.

Hi Nick,

Back in March 08 you posted a query regarding using vlookup and going to the next largest value (rather than the default nearest lesser value).
Sweep/Dave replied with an answer for you which appears to have solved your problem.

Only the link to the solution doesnt seem to work anymore!
http://www.excelforum.com/showthread.php?t=636366

Any Chance you can remember this solution and forward it to me please?!

Many Thanks in Advance
Rich

So I have a column that that has up to 18 values in it, I would like to take the highest value from that set of 18 cells and have it display in another cell, then the next higest value from the 18 cells in another cell and so on up to five values. How should I go about doing this? Below is a crude example of what I am trying to accomplish.

10_________ 50
20_________ 40
30_________ 30
50
10
10
40
10
10
10
10
10
10
19
18
15

In Excel 2007 I'm using a vlookup to return a value from an array using approximate value vs exact and I want the next = or greater than value in the array. but vlookup returns the lower value from the lookup array. Is there a way to get the next higher value in the array using approximate value?

hello people, i have a column of standard numbers that doesn't follow a logical row; such as:

16
18
20
22
25
28
32
36
40

i want excel to read a number in a cell and pick the next greater value in this serie.
24,54 to 25
32,001 to 36
17,82 to 18 and so on... i don't know how to do this with roundup and vlookup doesn't give the desired result everytime.
thanks in advance

Morning Folk

using vlookup I am trying o find the highest value>>>>

e.g

Column A Column B

111036 01/05/09
111036 08/08/09
111036 09/10/09 <<<<

Is vlookup the correct way to go,,, if not could someone point me in the correct direction

Many Thanks

Hi Everyone,

I am a new Excel user, having been thrown in at the deep end a few months ago. I am starting to use formulas and I am constantly amazed at the power of Excel. I am trying to write a spreadsheet and I'm having a problem getting Excel to do what I want;

I have 2 column, each cell in Column 1 will have a numeric value changed daily. I want Column 2 to retain the highest value that has been added. i.e. If I type 4 in A1,(for example), on Monday, then B2 will read 4, if I type 5 in A1 the next day, then B2 will change to 5, but if I type a value less than 5 in A1, then I still want B2 to read 5. The value will be input daily for an indefinite period and needs to be user friendly. I can't work out how to achieve this.

I hope that this is clear.

Thanks.

Hi,

Is there a way to find the next highest match to a selected value
in a column which is sorted in descending order.

i.e. find next highest to 125

178
152
125
124
105
56

Value returned would be 124

Hi all -

From on-line help I see
If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. But this is not correct as the value I am seeking falls with a range of Min/Max Values therefore the return should be the Next Largest Value

I have values I would like to lookup against a table Min Max values
Col A Min Value 700,000
Col B Max Value 799,999.99

The value being sought is 745,000

I would like the return to be 799,999.99
But currently returning 700,000

Current formula is
=VLOOKUP(G2,tblMaxValues,2,TRUE)
Where G2 is 745,000
tblMaxValues is a list of values for comparison
,2, is the column to be returned

Thanks
-marc