Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup help
My vlookup formula no longer works, I think it may be something to do with
the fact that the data is no longer in sequential order e.g. 1, 2, 6, 5 and there are cells in the range that are blank. Surely there must be a way around this? Is there a way to get the vlookup to work properly without putting all the rows in sequential order? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup help
It might be you are using TRUE as your range_lookup (default is TRUE if
neither is entered). TRUE means you are looking for an approximate match. You can only do this if the list is sorted (no exceptions, I think). FALSE looks up an exact match and does not need to be sorted. Here is an exert from the help... VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted. Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. "Luke" wrote: My vlookup formula no longer works, I think it may be something to do with the fact that the data is no longer in sequential order e.g. 1, 2, 6, 5 and there are cells in the range that are blank. Surely there must be a way around this? Is there a way to get the vlookup to work properly without putting all the rows in sequential order? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup help
Are you using the 4th parameter in your formula?
=Vlookup(lookup value,datalist array,column index,range lookup) Set range lookup to zero ( 0 ), or "False" (no quotes). Will then return exact matches only, or a #N/A! error message if none is found, BUT ... datalist does not have to be sorted. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Luke" wrote in message ... My vlookup formula no longer works, I think it may be something to do with the fact that the data is no longer in sequential order e.g. 1, 2, 6, 5 and there are cells in the range that are blank. Surely there must be a way around this? Is there a way to get the vlookup to work properly without putting all the rows in sequential order? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |