Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Smile Unanswered: Complex VLOOKUP Question

    I need to know if you can do a VLOOKUP from 2 cell references or find an alternate solution as this does not seem possible with the VLOOKUP function. For example, product # 1000 is used at branches CA_01, CA_20, FL_01, FL_20,TX_75
    The branch is typed in sheet #1, cell A2 and product typed in B2. Sheet #2 has ranges named CA_01, CA_20, FL_01...etc. and each named range has the (same) product numbers as the key, and other columns that I want to look up.

    Using "CA_01" in one cell (A2 in sheet #1) and 1000 (in B2) together in a VLOOKUP statement in C2 does not find anything in named range "CA_01"
    The statement reads "=VLOOKUP(B2,A2,2,False)" "#N/A" is returned.

    I am hoping to hear from the experts.

    Thanks.
    Jerry

  2. #2
    Join Date
    Jul 2003
    Posts
    9

    Re: Complex VLOOKUP Question

    Vllokup uses parameters -
    The value that you are looking for in the first column of the lookup table

    The Lookup table

    The column number in the lookup table to be returned if a match is found

    FALSE - an exact match is needed otherwise N/A is returned if TRUE then nearest above is returned

    You seem to be searching for 1000 in the table 'A2' that contains the value CA_01. This will return N/a as they are not the same.
    You've not said exactly what you are after as an end result ? Is it the names of the branches that 'use' product 1000 (or whatever product is selected) or are you trying to find out what barnches 'use' what produuct. Either way I suspect you will have multiple results as in the example for #1000 that you have given, in which case VLOOKUP is not the answer.
    If however you are just trying to determine whether a branch 'uses' particular product you could concatenatet the two columns in your second worksheet giving you a single columns containing all possible combinations. VLOOKUP can the use a concatinated value A2&B2 against the lookup table's concatenated column.

    Originally posted by JerryDal
    I need to know if you can do a VLOOKUP from 2 cell references or find an alternate solution as this does not seem possible with the VLOOKUP function. For example, product # 1000 is used at branches CA_01, CA_20, FL_01, FL_20,TX_75
    The branch is typed in sheet #1, cell A2 and product typed in B2. Sheet #2 has ranges named CA_01, CA_20, FL_01...etc. and each named range has the (same) product numbers as the key, and other columns that I want to look up.

    Using "CA_01" in one cell (A2 in sheet #1) and 1000 (in B2) together in a VLOOKUP statement in C2 does not find anything in named range "CA_01"
    The statement reads "=VLOOKUP(B2,A2,2,False)" "#N/A" is returned.

    I am hoping to hear from the experts.

    Thanks.
    Jerry

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Thanks for your good advice. Using this technique, for example, "=VLOOKUP($A1&$A2,MYTABLE,2,False)" and having a concatenated column as the key in my lookup table, is a workable solution.

    Jerry

Posting Permissions

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