Hi,

I have a list of values as follows:
X Y
50 2
80 4
100 6

When I lookup the value of x=85, I would like the returned value to be 6
instead of 4. How do I do that?

There has to be an easier way and I hope someone posts it but for now try:-

=IF(ISNA(VLOOKUP(C1,A1:B30,2,FALSE)),LARGE(B1:B30, RANK(VLOOKUP(C1,A1:B30,2,TRUE),B1:B30)-1),"")

Lookup value in C1

Array in A1:B30

Mike

"yhm" wrote:

Hi,

I have a list of values as follows:
X Y
50 2
80 4
100 6

When I lookup the value of x=85, I would like the returned value to be 6
instead of 4. How do I do that?

Wed, 4 Jul 2007 04:22:00 -0700 from yhm
:
Hi,

I have a list of values as follows:
X Y
50 2
80 4
100 6

When I lookup the value of x=85, I would like the returned value to be 6
instead of 4. How do I do that?

I don't know of a way to do it directly in VLOOKUP, but you can
change your table to this:
-100 6
-80 4
-50 2
and then look up the negative of the value you're actually trying to
look up, e.g. -85 instead of 85.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Oops an error. But the solution just lengthens the formula!!

=IF(ISNA(VLOOKUP(C1,A1:B30,2,FALSE)),LARGE(B1:B30, RANK(VLOOKUP(C1,A1:B30,2,TRUE),B1:B30)-1),VLOOKUP(C1,A1:B30,2,FALSE))

Mike

"yhm" wrote:

Hi,

I have a list of values as follows:
X Y
50 2
80 4
100 6

When I lookup the value of x=85, I would like the returned value to be 6
instead of 4. How do I do that?

Hi

If the Xs can be sorted descending you can use:

=INDEX(A2:B4;MATCH(C4;A2:A4;-1);2)

provided the array is in A2:B4, the lookup value is in C4, the lookup range
is in A2:A4 and the value you want returned is in the second column of the
array (B in this case).

HTH.

/Sune

"yhm" wrote:

Hi,

I have a list of values as follows:
X Y
50 2
80 4
100 6

When I lookup the value of x=85, I would like the returned value to be 6
instead of 4. How do I do that?

Hi Mike,

I have tried your formula, but there seems to be a problem.....eg my data is
as follows:
X Y
700 616
750 1,218
800 868
850 1,500
When I try to search for a value of X=799, instead of returning Y=868, the
computer returns Y=1500. Why is that? Is there any way to correct it? Thanks!
;-)
"Mike H" wrote:

Oops an error. But the solution just lengthens the formula!!

=IF(ISNA(VLOOKUP(C1,A1:B30,2,FALSE)),LARGE(B1:B30, RANK(VLOOKUP(C1,A1:B30,2,TRUE),B1:B30)-1),VLOOKUP(C1,A1:B30,2,FALSE))

Mike

"yhm" wrote:

Hi,

I have a list of values as follows:
X Y
50 2
80 4
100 6

When I lookup the value of x=85, I would like the returned value to be 6
instead of 4. How do I do that?