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" wrote in message
...
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?

On Fri, 30 Jun 2006 00:30:28 -0400, "Biff"
wrote:

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

"Bud" wrote in message
. ..
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.