Vlookup

  • Thread starter Thread starter Lorry
  • Start date Start date
L

Lorry

I am using the vlookup function and need it to return the match for the next
highest value, not the closest below the value. Example: Value is 61 and I
need it to return the information in column two below (.87) but it's
returning .8, because 59 is below 61. I need it to move to the next up, 64.
Can someone help?

54 .59
59 .8
64 .87
 
Hi

try this formula in D2 and that C2 hold the lookup value, 61
the list are in Col A and Col B

=INDEX($B$2:$B$100,IF(ISNUMBER(MATCH($A$2:$A$100,0)),MATCH(C2,$A$2:$A$100,0),1+MATCH(C2,$A$2:$A$100,1)))

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
Hi

try this formula in D2 and that C2 hold the lookup value, 61
the list are in Col A and Col B

=INDEX($B$2:$B$100,IF(ISNUMBER(MATCH($A$2:$A$100,0)),MATCH(C2,$A$2:$A$100,0),1+MATCH(C2,$A$2:$A$100,1)))

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
Hi,

What do we do if there's and exact match? This finds the next highest if
there isn't an exact match and if there is an exact match it returns the match

=MIN(IF(A1:A7>=C1,B1:B7))

if you 'always' want the higer vaue even when there's an exact match use this

=MIN(IF(A1:A7>C1,B1:B7))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike
 
Hi,

What do we do if there's and exact match? This finds the next highest if
there isn't an exact match and if there is an exact match it returns the match

=MIN(IF(A1:A7>=C1,B1:B7))

if you 'always' want the higer vaue even when there's an exact match use this

=MIN(IF(A1:A7>C1,B1:B7))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike
 
Lorry said:
I am using the vlookup function and need it to return the match for the next
highest value, not the closest below the value. Example:  Value is 61 and I
need it to return the information in column two below (.87) but it's
returning .8, because 59 is below 61. I need it to move to the next up, 64..
Can someone help?

54  .59
59  .8
64  .87
Click to expand...

If your table were in C3:D5 and sorted in ascending order on the 1st
column and your lookup value in cell C7, you could try

=INDEX(D3:D5,MATCH(C7,C3:C5)+(VLOOKUP(C7,C3:D5,1)<C7))
 
Lorry said:
I am using the vlookup function and need it to return the match for the next
highest value, not the closest below the value. Example:  Value is 61 and I
need it to return the information in column two below (.87) but it's
returning .8, because 59 is below 61. I need it to move to the next up, 64..
Can someone help?

54  .59
59  .8
64  .87
Click to expand...

If your table were in C3:D5 and sorted in ascending order on the 1st
column and your lookup value in cell C7, you could try

=INDEX(D3:D5,MATCH(C7,C3:C5)+(VLOOKUP(C7,C3:D5,1)<C7))
 
Back
Top