CODEWITHSUNDEEP
excelexcel-formulavlookup
akira
akira

Reputation: 51

Three Dimension Vlookup

I have a master table with the following structure in excel : enter image description here

How can I convert it as shown in the second picture by using the vlookup function? (function in yellow cells). Three keys involved now: Daytime, User and Data Type (ADP_ERQ, ADP_SO)

Note: Column headers (Daytime, User, ADP_ERQ, ADP_SO) in the second picture are fixed.

enter image description here

Upvotes: 1

Views: 73

Answers (2)

user4039065
user4039065

Reputation:

Try this in C17 then fill right and down.

=INDEX($A$3:$Z$12, MATCH($A17, $A$3:$A$12, 0), AGGREGATE(15, 7, (COLUMN(1:1)+MATCH($B17, $1:$1, 0)-2)/($2:$2=C$16), 1))

enter image description here

Upvotes: 2

Luuklag
Luuklag

Reputation: 3914

For cell C17: IF(B17="USER_A",VLookup(A17,A3:C12,2,0),VLookup(A17,A3:E12,4,0))

For cell D17: IF(B17="USER_A",VLookup(A17,A3:C12,3,0),VLookup(A17,A3:E12,5,0))

In this formula replace A3:C12 and A3:E12 with your appropriate table range, then drag these down.

Upvotes: 1

Related Questions