CODEWITHSUNDEEP
excel
excelguy
excelguy

Reputation: 1624

Excel , Multi-lookup/Match formula

I would like to use a formula in Sheet B to obtain the following transformation. I want to vlookup stack, over, flow, super, user and the associated id's and put them into the Sheet B format. The formula would be copied horizontally across many 'Names' and then down.

Current, sheet A:

+-------------+-------+-------+
| Position_ID | Name  | Value |
+-------------+-------+-------+
|  5963650267 | stack |    10 |
|  5963650267 | over  |    20 |
|  5963650267 | flow  |    30 |
|  5963650267 | super |    40 |
|  5963650267 | user  |    50 |
|  5963650268 | stack |    90 |
|  5963650268 | over  |   110 |
|  5963650268 | flow  |    80 |
|  5963650268 | super |    70 |
|  5963650268 | user  |    20 |
+-------------+-------+-------+

Expected, Sheet B, headers and positions ids are already pre populated:

+-------------+-------+------+------+-------+------+
| Position_ID | stack | over | flow | super | user |
+-------------+-------+------+------+-------+------+
|  5963650267 |    10 |   20 |   30 |    40 |   50 |
|  5963650268 |    90 |  110 |   80 |    70 |   20 |
+-------------+-------+------+------+-------+------+

Upvotes: 2

Views: 161

Answers (2)

EEM
EEM

Reputation: 6660

Assuming the data in Sheet A is located at A1:C11 (adjust as required), enter this Formula Array in Sheet B at B2 then copy to all required cells (i.e. C2:F2 and B3:F3)

=INDEX('Sheet A'!$C$1:$C$11,
MATCH(CONCATENATE($A2,"|",B$1),
CONCATENATE('Sheet A'!$A$1:$A$11,"|",'Sheet A'!$B$1:$B$11),0))

Formula Array must be entered by holding down CTRL + SHIFT + ENTER

enter image description here

Upvotes: 1

Hatt
Hatt

Reputation: 709

Apologies for the formatting - but if you add the vlookups to the empty shell of position_ids by name on sheet b it should give you the grid you're looking for.

Sheeta! ID&Name Position_ID Name    Value   
        =C2&D2      1       stack   10  
        =C3&D3      1       over    20  
        =C4&D4      1       flow    30  
        =C5&D5      1       super   40  
        =C6&D6      1       user    50  
        =C7&D7      2       stack   90  
        =C8&D8      2       over    110 
        =C9&D9      2       flow    80  
        =C10&D10    2       super   70  
        =C11&D11    2       user    20  

Sheetb! stack   over    flow    super   user
1   =VLOOKUP($A14&B$13,$B$2:$E$11,4,FALSE)  =VLOOKUP($A14&C$13,$B$2:$E$11,4,FALSE)  =VLOOKUP($A14&D$13,$B$2:$E$11,4,FALSE)  =VLOOKUP($A14&E$13,$B$2:$E$11,4,FALSE)  =VLOOKUP($A14&F$13,$B$2:$E$11,4,FALSE)
2   =VLOOKUP($A15&B$13,$B$2:$E$11,4,FALSE)  =VLOOKUP($A15&C$13,$B$2:$E$11,4,FALSE)  =VLOOKUP($A15&D$13,$B$2:$E$11,4,FALSE)  =VLOOKUP($A15&E$13,$B$2:$E$11,4,FALSE)  =VLOOKUP($A15&F$13,$B$2:$E$11,4,FALSE)

Sheetb! stack   over    flow    super   user
1   10  20  30  40  50
2   90  110 80  70  20

Upvotes: 0

Related Questions