Lookup or reference function needed

[Fire Guy]

I have a table that I return specific values when two conditions are true:

E H M L
SI 700 600 500 400
DI 550 400 300 250
WM 550 400 300 200
BS NP 400 300 250
EB NP NP 250 250

For example, when two test are true, they return one of the values: i.e. if
SI and E occur, return 700. If SI and H occur, return 600.

I've tried the lookups, or, and, and several other functions, and can't seem
to find one that allows me two variables to return a value.

 

[Niek Otten]

If your data is in A1:E6, the "SI" is in A9 and the "H" is in B9:

=INDEX(B2:E6,MATCH(A9,A2:A6,0),MATCH(B9,B1:E1,0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a table that I return specific values when two conditions are true:
|
| E H M L
| SI 700 600 500 400
| DI 550 400 300 250
| WM 550 400 300 200
| BS NP 400 300 250
| EB NP NP 250 250
|
| For example, when two test are true, they return one of the values: i.e. if
| SI and E occur, return 700. If SI and H occur, return 600.
|
| I've tried the lookups, or, and, and several other functions, and can't seem
| to find one that allows me two variables to return a value.
| --
| Thanks for your help.

 

[David Biddulph]

If your data table is in the top left hand corner (A1:E6), then try
=INDEX($B$2:$E$6,MATCH(row_input,$A$2:$A$6,0),MATCH(column_input,$B$1:$E$1,0))

 

[Fire Guy]

Thanks, I'll try these solutions. I was able to make it work with IF
functions, but I'm certain there is an easier way. Happy holidays to all.