Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup
I am looking for a way to use vlookup to find data that is not a match but
will go the the next higher value. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup
rRichard,
This isn't clear. Suppose we have the data set 1 8 2 9 5 10 6 11 7 12 8 13 If we look up 5 in the first column what value do you want returned and why ditto for looking up 3 in the first column Mike "Richard V" wrote: I am looking for a way to use vlookup to find data that is not a match but will go the the next higher value. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup
That's not an option in VLOOKUP, if you use FALSE as the final parameter, it
will find exact matches only, and if you use TRUE it will drop down to the closest match. There isn't a round UP option. But you could use an INDEX(MATCH) function and trick it into doing that, but it might get weird when there IS an exact match. If the data you want to return is in column B based on a lookup of a value in column A and you want it to find the closest match and round up, you have to offset the ranges by one row to trick it into doing that. I'm putting the value to match in C1 =INDEX($B$2:$B$101,MATCH(C1,$A$1:$A$100,1)) That will do what you want when there ISN'T an exact match. If there is won't get the right answer. So you probably need to do a test first. =IF(ISNA(MATCH(C1,$A$1:$A$100,0)), INDEX($B$2:$B$101,MATCH(C1,$A$1:$A$100,1)), INDEX($B$1:$B$100,MATCH(C1,$A$1:$A$100,0))) Hope that gets you closer. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX(MATCH) clarification
Sorry, part of my message disappeared. I was saying that if the value you
want to match is an EXACT match in the lookup column, it would not give you the correct answer, so the second longer formula should be used all the time since it checks for an exact match before choosing which formula to use. =IF(ISNA(MATCH(C1,$A$1:$A$100,0)), INDEX($B$2:$B$101,MATCH(C1,$A$1:$A$100,1)), INDEX($B$1:$B$100,MATCH(C1,$A$1:$A$100,0))) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup
Hi,
Why don't you show us what your data looks like and what result you expect. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Richard V" wrote: I am looking for a way to use vlookup to find data that is not a match but will go the the next higher value. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |