Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Vlookup help ... or something similar
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Vlookup help ... or something similar
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Vlookup help ... or something similar
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" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Vlookup help ... or something similar
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. 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Vlookup help ... or something similar
Brilliant! BRILLIANT!! Thank you (and Harlan Grove)!!!
On Thu, 29 Jun 2006 21:34:01 -0700, JMB wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Vlookup help ... or something similar
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Vlookup help ... or something similar
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Vlookup help ... or something similar
Oh! Okay. Thanks for the explanation. (Note to self, I've got a LOT
to learn, buddy!) On Fri, 30 Jun 2006 02:03:32 -0400, "Biff" wrote: 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Vlookup help ... or something similar
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. "Bud" wrote: Oh! Okay. Thanks for the explanation. (Note to self, I've got a LOT to learn, buddy!) On Fri, 30 Jun 2006 02:03:32 -0400, "Biff" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using VLOOKUP to match similar text? | Excel Worksheet Functions | |||
Similar to Vlookup | Excel Discussion (Misc queries) | |||
Vlookup on a worksheet with similar values | Excel Worksheet Functions | |||
FIND FORMULA SIMILAR TO VLOOKUP FOR NON-ASCENDING VALUES | Excel Worksheet Functions | |||
Vlookup on multiple similar entries / NO VBA | Excel Worksheet Functions |