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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Site1[/TD]
[TD]Site2[/TD]
[TD]Site3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Task1[/TD]
[TD]D1[/TD]
[TD]D2[/TD]
[TD]D3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Task2[/TD]
[TD]D4[/TD]
[TD]D5[/TD]
[TD]D6[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Task3[/TD]
[TD]D7[/TD]
[TD]D8[/TD]
[TD]D9[/TD]
[/TR]
</tbody>[/TABLE]

Table 2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Task1[/TD]
[TD]Task2[/TD]
[TD]Task3
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Site1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Site2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Site3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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