CODEWITHSUNDEEP
excelexcel-formulapivot-table
Dinks123
Dinks123

Reputation: 79

Excel Vlookup Formula required from pivoted data

I have a simple data table from a pivot table in the following format:

 A                          B           C            D
 Names                      Dog         Cat          Horse
 John Smith                 1           1            0
 Jane Smith                 0           0            0
 Jane Dow                   0           1            0
 John Dow                   1           1            1

I want to use a formula to populate another table using the one above. The second table has a different ordering to it. For example Horse is in Column B instead of D and John Smith is on Row 5 instead of 1. 

 A                          B           C            D
 Names                      Horse       Cat          Dog
 John Dow                   ?            
 Jane Smith                
 Jane Dow                   
 John Smith

What is the best formula to use given these circumstances, I do not think a Vlookup/hlookup will work because it is dependent on both horizontal and vertical variables.

Upvotes: 1

Views: 1670

Answers (3)

Hariharan G R
Hariharan G R

Reputation: 549

Try this formula in G2 cell (VLOOKUP with MATCH)

=VLOOKUP($F2,$A:$D,MATCH(TRIM(G$1),$A$1:$D$1,0),0)

enter image description here

Upvotes: 3

jeffreyweir
jeffreyweir

Reputation: 4824

A much more robust solution is to use the GETPIVOTDATA function...it does exactly what you are asking in your question, and users can radically change the layout of the PivotTable without the formula breaking. enter image description here

Upvotes: 2

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

As @pnuts has mentioned, this seems like a pointless exercise as you can just manipulate data in the pivot table itself.

Having said that though, ask and you shall receive...

For this task you will want to use the INDEX() formula. What this does read from data by giving it a row and column number relative to the starting position (wherever it is it will assume the first cell is row 1, column 1).

This allows us to use other formula to determine the row number and column number we are looking for.

=INDEX($E$5:$G$8,               'The area our results are that we are looking through
       MATCH($I4,$D$5:$D$8,0),  'Match the name to the name column, return a number of it's position counting from 1 as the first cell
       MATCH(J$3,$E$4:$G$4,0))  'Same as above but matching the animal name

Now looking at the example, in the grid E5:G8, we are looking for row 4, column 3 to be returned by INDEX().

This is because John Dow is the 4th name found in the first MATCH() and Horse is the 3rd animal name found in the second MATCH().

This returns the bottom right cell in our results respectively.

=INDEX($E$5:$G$8,MATCH($I4,$D$5:$D$8,0),MATCH(J$3,$E$4:$G$4,0))

enter image description here

Upvotes: 1

Related Questions