Need Vlookup help ... or something similar

[Bud]

VLOOKUP and the like return the next lowest value when the exact
search value cannot be found. I need to be able to return the next
highest value. For example, I want to return 112000 when I search the
following table for the value 760 (in cell A2, say):

B C
75 20000
150 37000
300 60000
450 76000
600 87000
750 94000
1250 112000

=vlookup(a2,b1:c7,2) finds 750 and returns 94000 ... I need it to find
1250 and return 112000.

 

[Biff]

Hi!

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=IF(A2="","",INDEX(C1:C7,MATCH(1,--(B1:B7>=A2),0)))

Biff

 

[Guest]

Assuming your data table is in A1:B7 and the value you want to look up is in
cell
C1, try (pretty sure I got this from one of Harlan Grove's posts)

=INDEX(B1:B7,MATCH(C1,A1:A7)+(VLOOKUP(C1,A1:A7,1)<>C1))

But I would recommend using a 0 for your first value if you want anything
below 75 to return 20000.

 

[Bud]

Thanks, but I don't understand what you mean by "Entered as an array
using the key combination of CTRL,SHIFT,ENTER:" That combination
doesn't seem to do anything or enable anything special in my
spreadsheet. When I enter the formula you present I get a #VALUE!
error. I'll go read up on Index and Match, but I'd sure appreciate a
few words of clarification.

 

[Bud]

Brilliant! BRILLIANT!! Thank you (and Harlan Grove)!!!

 

[Bud]

Thanks, but I don't understand what you mean by "Entered as an array
using the key combination of CTRL,SHIFT,ENTER:" That combination does
do anything or enable anything special in my spreadsheet (that I can
see). The formula you gave resolves to a #VALUE! error unless I
entered it incorrectly. Could you provide some clarification?

 

[Biff]

Thanks, but I don't understand what you mean by "Entered as an array

using the key combination of CTRL,SHIFT,ENTER:"

Normally when you enter something in a cell you type the entry then hit the
ENTER key.

When you enter an array formula instead of just hitting ENTER you use the
key combination of CTRL,SHIFT,ENTER. That is, hold down both the CTRL key
and the SHIFT key then hit ENTER. When done properly Excel will enclose the
formula in squiggly braces { }. You cannot just type these braces in, you
MUST use the key combination.

Biff

 

[Bud]

Oh! Okay. Thanks for the explanation. (Note to self, I've got a LOT
to learn, buddy!)

 

[Guest]

This might help. Although SUMPRODUCT is not entered with the CSE key
combination, it is a function that accepts array arguments and is quite
useful. Also, a link to Chip's site w/more on array formulae.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://www.cpearson.com/excel/array.htm

Also, lots of other stuff on these sites if you go to the main pages.