Results 1 to 2 of 2
  1. #1
    New Lounger
    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

  2. #2
    Super Moderator macropod's Avatar
    Join Date
    May 2002
    Canberra, Australian Capital Territory, Australia
    Thanked 470 Times in 387 Posts

    Re: Validate lookup table (XP)

    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,<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.


    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts