VLOOKUP searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.
VLOOKUP will work with a list where the table arguments are sorted, and you will get the closest match to a table argument that does not exceed your lookup value.
(for sorted lists use TRUE or default for a *close* match)
Syntax: (As always look in HELP for more information)
range_lookup can be TRUE or FALSE, if omitted the default is TRUE
VLOOKUP will work with a list where the arguments are unordered, and you either get an exact match or fail with #N/A!.
(Whether sorted or not when an *exact* match is required so is the use of FALSE)
To suppress N/A errors:
=IF(ISNA(VLOOKUP(...,...,...,False)),"Item not found",VLOOKUP(...,...,...,False))
A cause of #VALUE! error is a zero value for col_index_num value in the function.
Do not mix cells defined as numbers with cells defined as text in the argument column of your table. Some tips on determining data type and actual content of your data. Your table must be consistent, but your lookup value can be forced to look like the table by using one or the other of these tricks (Peo Sjoblom 3003-01-15).
This is the simplest example that I can come up with. Note the use of TRUE in the formulas indicating that the value found in the table does not have to be an exact match but must be less than or equal to the lookup_value used. For VLOOKUP the first column of the range is the used to match the argument, the 2 used in the example indicates to return the second column of the table. Since TRUE is used an exact match is not required, but because an exact match is not equired, the table must be in ordered in ascending order to obtain the correct result. If VLOOKUP can’t find lookup_value, and range_lookup is TRUE, it uses the largest table argument value that is less than or equal to lookup_value.