Validate lookup table (XP)

Topic

New Reply

Hi!
I collate sales figures for our global operations & have information provided in different formats from different partners.
I use VLOOKUP tables to convert the codes used in other countries into a standard format which we use in our head office.
One overseas branch outputs their Quickbooks reports to Excel & emails this to me monthly.

The system I have works well, EXCEPT when they insert a new product code or customer which has NOT already been registered in my lookup table.
In this case, VLOOKUP returns the nearest best match – which is not good enough for me (or my boss).

Can someone give me a clue as to how I can specify a validation routine to run before I throw their file at my macro?
I’d like to highlight any codes on their file which do not have a perfect match in my Lookup table
I am thinking of something along the lines of a conditional format, but I’m not sure how to refer it to a lookup range, held in a separate file.

Thanks – Simon

Reply | Quote

Replies

Hi Simon,

If you insert a zero as the last argument of your VLOOKUP function, that will allow you to specify an exact match as a requirement.
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
For your problem, you’d set the range_lookup to 0.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote