Vlookup, next greater than number 1

[SteveNapper]

Have looked at thread68-1365089, but doesn't seem to do what I want....

I have a velocity of say 0.24 and I need Excel to return a depth of 0.06 from the column on the left.

Depth Velocity
0.02 0.128
0.04 0.213
0.06 0.283
0.08 0.345

Have tried Vlookup, but as not the exact value doesn't seem to want to return a value.

Any help would be appreciated

 

[GlennUK]

I don't see how you could have used VLOOKUP, as the lookup value column is not the first column of the table.

Can you sort the table in descending order? If so, then there are solutions.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Rofeu]

Hi,

as Glenn says, you'll need to sort your table in descending order on Velocity. You can then use the following as an array formula (ctrl+shift+enter):

{=INDEX($A$2:$A$5,MATCH(-$D$1,-VALUE($B$2:$B$5),TRUE),1)}

assuming:
Values for Depth in $A$2:$A$5
Values for Velocity in $B$2:$B$5
Lookup Value in $D$1

Cheers,

Roel

 

[GlennUK]

Hi Rofeu,

won't a non-array formula of:

=INDEX(A2:A5,MATCH(D1,B2:B5,-1))

work?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SteveNapper]

Roefu / Glenn,

Have tried both your suggestions in a fresh spreadsheet, with the data sorted ascending on velocity as you correctly point out (typo on my part) and can't get either of them to work.

Have placed data in cells a2:b5 exclding headings and the data value to look up in D1 as per your code.

VLOOKUP does work to an extent, but doesn't allways round up - goes to nearest value....

Any thoughts / comments gladly welcomed

Steve

 

[GlennUK]

Hi SteveNapper,

you say that you have your data sorted ascending, which is the opposite of what Rofeu and myself advised. Please tell us whether you really did that, or just mistyped ascending for descending in your latest post.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Rofeu]

Argh, Glenn! Of course! I once started (a long time ago) with using match with match_type set to FALSE. I completely forgot that that part has 3 options.

Steve: sort it DEscending, not Ascending.

Cheers,

Roel

 

[SteveNapper]

VelocityDepth 0.283
0.345 0.08 #N/A (1)
0.283 0.06 #N/A (2)
0.213 0.04
0.128 0.02

(1)
{=INDEX(A2:A5,MATCH(-D1,-VALUE(B2:B5),TRUE),1)used (ctrl+shift+enter) to get {} brackets in Excel as suggested. Removed absolute refernces to be sure...

(2)
=INDEX(A2:A5,MATCH(D1,B2:B5,-1))

Tried both, and copied formulae / values direct from Excel... Have checked and DATA is sorted DEscending

#N/A I understand is the Excel eqivalent of 'out of range'

I must be missing something, please help.

Steve

 

[GlennUK]

You have swapped the velocity and depth columns without adjusting the formulae. Swap the references in the formulae.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SteveNapper]

Glen,

At least I was right about one thing. I was missing something.

Many, many thanks and accept this star

Steve

 

[GlennUK]

Glad you got it all working. :-D

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.