Need Help With Lookuop but not sure what kind of Lookup

[maximillianrg]

Thank you in advance for your help. I'm working with two separate excel sheets. In the first sheet the data is laid out as depicted in the first table below (Table1). My second sheet is laid out as the second table (Table2)

In Table1, Task2 Site2 = D5, Task1 Site3 = D3, Task3 Site1 = D7 and so forth

I'm looking for a formula that when put in cell B2 of Table 2 would look up both Task1 and Site1 in table1 and return the value of D1
The same formula if put in cell D3 of Table2 would look up both Task3 and site2 in Table1 and return the value of D8

Thank you


Table1

	A	B	C	D
1		Site1	Site2	Site3
2	Task1	D1	D2	D3
3	Task2	D4	D5	D6
4	Task3	D7	D8	D9

<tbody>
</tbody>

Table 2

	A	B	C	D
1		Task1	Task2	Task3
2	Site1
3	Site2
4	Site3

<tbody>
</tbody>

 

[FDibbins]

Try this in the 1st cell of the 2nd table...
=IFERROR(INDEX($B$3:$D$5,MATCH(B$10,$A$3:$A$5,0),MATCH($A11,$B$2:$D$2,0)),"Not Found")
include the sheet names as needed, and copy down and across as needed

 

[Guru008]

Hi,

Try this

=IFERROR(INDEX($B$22:$D$24,MATCH(B$29,$A$22:$A$24,0),MATCH($A30,$B$21:$D$21,0)),"")

 

[FDibbins]

@ Guru008, OP's range indicates A1:D4, so, while if adjusted, the range in your suggestion - $B$22:$D$24 - would work, it could confuse a new excel user