LookUp/Match Question

[Guest]

My Data Table looks as so. Products across Row1 and Salespeople down ColA.

DataTable
A B D Z
1 100 500 100 99
2 50 200 100 200
6 0 500 100 99
10 0 0 100 0

I am trying to create the table below with a formula in B2 that will look at
the values in A2 and B1, then look at my Data Table and find the
corresponding value in that table (if there is one), return that value or
return "NoMatch".

Is it possible ?

NewTable
A B C Z
1 100 500 NoMatch 99
2 50 200 NoMatch 200
3 NoMatch NoMatch NoMatch NoMatch
10 0 0 NoMatch 0

Thank you in advance.

 

[chris.uhrich]

If you assume that the "DataTable" above was in range A1:E5, and that
you placed your "NewTable" in range A10:E14, you could enter this
formula into cell B11:

=IF(ISERROR(INDEX($A$1:$E$5,MATCH($A11,$A$1:$A$5,0),MATCH(B$10,$A$1:$E$1,0))),"NoMatch",INDEX($A$1:$E$5,MATCH($A11,$A$1:$A$5,0),MATCH(B$10,$A$1:$E$1,0)))

And then drag it to fill the cells from B11:E14.

 

[Ruffed Grouse]

If you assume that the "DataTable" above was in range A1:E5, and that
you placed your "NewTable" in range A10:E14, you could enter this
formula into cell B11:

=IF(ISERROR(INDEX($A$1:$E$5,MATCH($A11,$A$1:$A$5,0),MATCH(B$10,$A$1:$E$1,0))),"NoMatch",INDEX($A$1:$E$5,MATCH($A11,$A$1:$A$5,0),MATCH(B$10,$A$1:$E$1,0)))

And then drag it to fill the cells from B11:E14.