I am trying to write a validation formula that can evaluate 4 columns.

The issue is I need to use a list the is 50.000 items in length. I want to
store the list in an external workbook. In using VLOOKUP an error message
occurs when trying to save a spreadsheet linking to an extrenal worksheet in
which the list exceeds 16000 + rows. I have tried to place the list of
items in 4 columns, each with own range name.

How can I write a VLOOKUP formula that will look at all 4 columns. That is
if the item is not found in ColA then look in ColB if not ColB then ColC, etc

Here's the basic approach for 3:

=IF(ISNA(vlookup1),IF(ISNA(vlookup2),IF(ISNA(vlook up3),"not
present",vlookup3),vlookup2),vlookup1)

If you see the symmetry then you can see how to apply it to 4.

Hope this helps.

Pete

On Jul 4, 1:06*am, David Ingham <David
wrote:
I am trying to write a validation formula that can evaluate 4 columns.

The issue is I need to use a list the is 50.000 items in length. * I want to
store the list in an external workbook. * In using VLOOKUP an error message
occurs when trying to save a spreadsheet linking to an extrenal worksheet in
which the list exceeds 16000 + rows. * I have tried to place the list of
items in 4 columns, each with own range name. *

How can I write a VLOOKUP formula that will look at all 4 columns. *That is
if the item is not found in ColA then look in ColB if not ColB then ColC, etc