find a cell matching separate column and row values

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

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

"LQEngineer" <LQEngineer@discussions.microsoft.com> wrote in message
news:D408F9AD-163A-4369-8296-AD8CEADF4414@microsoft.com...
>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

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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"LQEngineer" wrote:
> 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