Returning a value in a table

[Cdshep15]

I would like to have a cell that would return a certain value based on what is entered in another cell.

Example...

If cell A1 = "yes" and cell B1 = "sir" then D1 would display "great"

but if cell A1 = "no" and cell B1 = "ma'am" then D1 would display "poor"

I have a table of all possible combinations in another part of the worksheet. Each combination of yes, no in A1 and great, poor in B1 would yield a different outcome that is listed in the table. I'm trying to use something like this....

=IF((AND(A1=F3:F61,B1=G3:G61)),H3:H61,"look it up")

the "look it up" part just means that the combo is not valid. Help would be much appreciated.

Thanks!!

 

[pgc01]

Hi Cdshep15

A solution is to look for the concatenation of the 2 values.

Please try:

=IF(ISNUMBER(MATCH(A1&B1,F3:F61&G3:G61,0)),INDEX(H3:H61,MATCH(A1&B1,F3:F61&G3:G61,0)),"Look it up")

This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

Hope this helps
PGC