transposing link formula

[encioman]

Dear Experts,
Kindly share me a formula which links the two sheets making the data fill as shown:

Sheet #1
A	1	SUGAR
B	2	COFFEE
C	3	SALT
D	4	OIL
Sheet #2
	1	2	3	4
C			SALT
B		COFFEE
A	SUGAR
D				OIL

<colgroup><col width="64" span="5" style="width: 48pt; text-align: center;"> </colgroup><tbody>
</tbody>

Many thanks in advance...

Regards,
encioman

 

[timfritsch]

Assuming the tables in both sheets start in cell A1, the formula in cell B2 of the second sheet is:

=IF(VLOOKUP($A2,Sheet1!$A$1:$C$4,2,FALSE)=B$1,VLOOKUP($A2,Sheet1!$A$1:$C$4,3,FALSE),"")

You can copy the formula to the remaining cells in the second sheet table.


Tim

 

[encioman]

Thank you so much...it worked in the first cell but when i copied...the result is blank

 

[encioman]

Dear Tim or anyone,

How would I do that "VINEGAR" must be in column 5 (F12) not that "OIL" in column 4

	A	B	C	D	E	F
1	A	1			SUGAR
2	B	2			COFFEE
3	C	3			SALT
4	D	4			OIL
5	D	5			VINEGAR
6
7	SHT. #2
8		1	2	3	4	5
9	C			SALT
10	B		COFFEE
11	D				OIL
12	D				OIL
13	A	SUGAR
14	C			SALT

<tbody>
</tbody>