Multiple VLookup (??) (Excel 2003)

Topic

New Reply

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.

Reply | Quote

Replies

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$12,$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),MATCH(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)

Reply | Quote

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

Reply | Quote