find a cell matching separate column and row values

[LQEngineer]

I have a table that contains values I wish to extract. These values depend
upon matching the return search criteria in column A and another search in
row 1. I wish to return the value in the intersecting cell.

Example:

A B C D E F G H
1 0.1 0.2 0.3 0.4 0.5 0.6
2 10 34 75 93 44 55 79
3 20 23 56 84 99 43 74
4 30 54 83 72 90 12 33

Range B2:B4 is named Mat_Col
Range 1C:1H is Named Val_Row

On another worksheet I have a value in Cell B3 that repesents a value in
Mat_Col
In cell B4 I have a value that is represented in Val_Row

If B3 or Mat_Col is 20 and B4 or Val_Row is equal to 0.4 I want Cell B5 to
contain the matching cell from the data that should be 99 (that exists in
cell F3)

What function do I use to find the results of Mat_Col and Val_Row entries?
How do I write or fill in the functions?

Thanks in advance
LQEngineer

 

[Biff]

Hi!

Try this:

=IF(COUNT(B3:B4)<2,"",INDEX(Sheet2!C2:H4,MATCH(B3,Mat_Col,0),MATCH(B4,Val_Row,0)))

Assumes the table is on Sheet2 and that you always use lookup values that
EXACTLY match Mat_Col and Val_Row. In other words, you won't be looking up
values like: 19 and 0.45.

Biff

 

[Max]

One way ..

Assuming source table in Sheet1, within B1:H4

In your other sheet,
placed in B5:
=IF(COUNT(B3:B4)<2,"",INDEX(Sheet1!$B$1:$H$4,MATCH(B3,Mat_Col,0),MATCH(B4,Val_Row,0)+1))