software

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?

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.

quot;Lukequot; wrote:

gt; My vlookup formula no longer works, I think it may be something to do with
gt; the fact that the data is no longer in sequential order e.g. 1, 2, 6, 5 and
gt; there are cells in the range that are blank.
gt;
gt; Surely there must be a way around this? Is there a way to get the vlookup to
gt; work properly without putting all the rows in sequential order?
gt;
gt;
gt;

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 quot;Falsequot; (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!
================================================== ===
quot;Lukequot; gt; 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?