Need Vlookup help ... or something similar

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bud - 30 Jun 2006 05:09 GMT

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.

Reply to this Message

Biff - 30 Jun 2006 05:30 GMT

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

> 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
[quoted text clipped - 12 lines]
> =vlookup(a2,b1:c7,2) finds 750 and returns 94000 ... I need it to find
> 1250 and return 112000.

Reply to this Message

Bud - 30 Jun 2006 06:02 GMT

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.

>Hi!
>
[quoted text clipped - 22 lines]
>> =vlookup(a2,b1:c7,2) finds 750 and returns 94000 ... I need it to find
>> 1250 and return 112000.

Reply to this Message

Bud - 30 Jun 2006 06:22 GMT

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?

>Hi!
>
[quoted text clipped - 22 lines]
>> =vlookup(a2,b1:c7,2) finds 750 and returns 94000 ... I need it to find
>> 1250 and return 112000.

Reply to this Message

Biff - 30 Jun 2006 07:03 GMT

> 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

> 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
[quoted text clipped - 28 lines]
>>> =vlookup(a2,b1:c7,2) finds 750 and returns 94000 ... I need it to find
>>> 1250 and return 112000.

Reply to this Message

Bud - 30 Jun 2006 07:55 GMT

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

>> Thanks, but I don't understand what you mean by "Entered as an array
>> using the key combination of CTRL,SHIFT,ENTER:"
[quoted text clipped - 42 lines]
>>>> =vlookup(a2,b1:c7,2) finds 750 and returns 94000 ... I need it to find
>>>> 1250 and return 112000.

Reply to this Message

JMB - 30 Jun 2006 22:07 GMT

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.

> Oh! Okay. Thanks for the explanation. (Note to self, I've got a LOT
> to learn, buddy!)
[quoted text clipped - 45 lines]
> >>>> =vlookup(a2,b1:c7,2) finds 750 and returns 94000 ... I need it to find
> >>>> 1250 and return 112000.

Reply to this Message

JMB - 30 Jun 2006 05:34 GMT

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.

Reply to this Message

Bud - 30 Jun 2006 06:14 GMT

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

>Assuming your data table is in A1:B7 and the value you want to look up is in
>cell
[quoted text clipped - 21 lines]
>> =vlookup(a2,b1:c7,2) finds 750 and returns 94000 ... I need it to find
>> 1250 and return 112000.

Reply to this Message