Vlookup with Hlookup or Index with Match?

Morning all,

I'm trying to gather information from a table and place it into another table with the same columns and row names.

So how do I use vlookup and hlookup to find the exact cell and place it into the new blank table or should I use index and match.

The reason I want to use this is because the columns in the data table could shrink, but I have the full table in the new blank table.

For instance

Header.

B1 = 1
C1 = 2
D1 = 3

Rows

A2 = a
A3 = b
A4 = c

Data

b2 = 100
b3 = 200
b4 = 300
c2 = 1
c3 = 2
c4 = 3
d2 = 50
d3 = 60
d4 = 70

I need a formula that would still grab column D's data even if column C was removed in the "data table" and place it into column D in the the new blank table - so the formula looks for the "rec id" in column A and looks across the headers for column D and places the data in the new table.

Is this possible or have I just dreamed up something excel can't do?

hope this helps

Originally Posted by :) Sixthsense :)

The structure which you shown in the sample file is seems to be arrived by applying Pivot Table. Instead of trying to use a formula in the Pivot Data use it with the Source Data of the Pivot.

If possible show the source data for providing formula.

No.

Thats not what I'm asking.

The data has come from pivot table but they are pasted values out of them, i.e. create selection then paste out. The end product is to have the table in AFTER with the data from table BEFORE using a excel function.

So for instance i need, lookup row AFTER - find row in BEFORE, lookup column in AFTER, find column in BEFORE and then get the data from the cell - bit like battleships if you will.

Originally Posted by Fotis1991

Just an idea...

In C6 and copy down and across.

=IFERROR(INDEX(BEFORE!$C$7:$J$15,MATCH($B6,BEFORE!$B$7:$B$15,0),MATCH(C$5,BEFORE!$C$6:$J$6,0)),"")

I think this actually works :D

muchos kudos - need to test though with some live data