Results 1 to 2 of 2
Thread: Validate lookup table (XP)
2003-09-22, 00:17 #1
- Join Date
- Feb 2002
- Melbourne, Australia
- Thanked 0 Times in 0 Posts
Validate lookup table (XP)
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
2003-09-22, 02:43 #2
- Join Date
- May 2002
- Canberra, Australian Capital Territory, Australia
- Thanked 439 Times in 362 Posts
Re: Validate lookup table (XP)
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,<f ont color=448800>range_lookup</font color=448800>)
For your problem, you'd set the <font color=448800>range_lookup</font color=448800> to 0.
[MS MVP - Word]