Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substitute for Vlookup
Is there any substitute for the vlookup function when the table
lookup_values are numbers but not in a continuing descending order? For example: A1 B1 1 5.5 8 5.6 14 5.7 19 5.8 Works: look for the number 1 in the table A1:B1 and return the second column, if not zero = the result is 5.5 Does not work: look for the number 2 in the table A1:B1 and return the second column, if not zero =the result is 5.5 as well instead of zero. I know I can manually fill the numbers between 1 and 8, but if it is a very big table, I don't want to spend too much time on that. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substitute for Vlookup
Sounds like you need to use the last parameter in the Vlookup function, and
set it to false, then it must find an exact match. If you omit the last parameter, it will find the closest match... However, it will return #N/A if it doesn't find a match, when you set the last parameter to false or 0... but there are ways to get around that... "amirstal" wrote in message ups.com... Is there any substitute for the vlookup function when the table lookup_values are numbers but not in a continuing descending order? For example: A1 B1 1 5.5 8 5.6 14 5.7 19 5.8 Works: look for the number 1 in the table A1:B1 and return the second column, if not zero = the result is 5.5 Does not work: look for the number 2 in the table A1:B1 and return the second column, if not zero =the result is 5.5 as well instead of zero. I know I can manually fill the numbers between 1 and 8, but if it is a very big table, I don't want to spend too much time on that. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE.... max. limit 8 :( | Excel Discussion (Misc queries) | |||
would like to substitute and return value of 0 for vlookup #N/A er | Excel Worksheet Functions | |||
VLOOKUP - WISH TO SUBSTITUTE FALSE FOR A VALUE | Excel Discussion (Misc queries) | |||
Is there a VLOOKUP substitute when data is not in ascending order | Excel Worksheet Functions | |||
A question about decoding, substitute or vlookup of character. | Excel Programming |