Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Oct 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple VLookup (??) (Excel 2003)

    I have searched for the proper terms and even a sample of a formula that will help me with a large task I am beginning. In planning this project, it would be very helpful to know if there is a way to create multiple vlookups in the same formula.

    Attached is a pretty simple spreadsheet that has 2 tables (table 1 and table 2).
    I am looking to populate B13 (red highlighted area) in Table 2, with information from table 1. I would need to search table 1 column A, then reference the same "pricing level" in Table 1 as found in B12. Ultimately the result of the formula would bring back information from cell D8 in this scenario.

    Using a copy of that formula I would then do the same "search" to populate the entire table 2. I have highlighted Yellow, Red, and Green, indicating where the formula would need to get the information, I just can't seem to figure out how to look both vertically and horizontally to find the correct cross-reference.

    Any help would be and is always greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple VLookup (??) (Excel 2003)

    You can use a combination of INDEX and MATCH. In cell B13:

    =INDEX($B$5:$F$8,MATCH($A13,$A$5:$A$8,0),MATCH(B$1 2,$B$4:$F$4,0))

    then fill down and right. This formula will return #N/A is there is no match. To suppress this:

    =IF(ISNA(INDEX($B$5:$F$8,MATCH($A13,$A$5:$A$8,0),M ATCH(B$12,$B$4:$F$4,0))),"",INDEX($B$5:$F$8,MATCH( $A13,$A$5:$A$8,0),MATCH(B$12,$B$4:$F$4,0)))

    This looks intimidating, but it "just" says

    =IF(original formula results in #N/A, blank, original formula)

  3. #3
    Lounger
    Join Date
    Oct 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple VLookup (??) (Excel 2003)

    Hans,

    A million thanks, I worked a little bit with the F1 help and some of MS Excel info this morning, thinking that Index was the way to go, but I would have never gotten this far.

    Thank you again,

    John

Posting Permissions

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