Help me with this simple excel formula

Hey smart excel people. Hoping you can help me with this simple problem. Essentially, I have this first table in which I have people selecting from 3 items (Low, Moderate, High). The “user” selects the option for columns 1 and 2, and I want column 3 to automatically select the correct option from the 2nd table. What’s the best way to go about that? I can do simple if/then statements, but throwing multiple arguements in there isn’t one of my many skills.

[TABLE=“class: grid, width: 192”]
[TR]
[TD]I/R (H,M,L)
[/TD]
[TD]C/R (H,M,L)
[/TD]
[TD]Assessed RMM
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD]L
[/TD]
[TD]H
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]M
[/TD]
[TD]M
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]H
[/TD]
[TD]M
[/TD]
[TD]H
[/TD]
[/TR]
[/TABLE]

[TABLE=“class: grid, width: 384”]
[TR]
[TD]Inherent Risk
[/TD]
[TD]×
[/TD]
[TD]Control Risk
[/TD]
[TD]=
[/TD]
[TD]Risk of Material Misstatement
[/TD]
[/TR]
[TR]
[TD]High
[/TD]
[TD][/TD]
[TD]High
[/TD]
[TD][/TD]
[TD]High
[/TD]
[/TR]
[TR]
[TD]High
[/TD]
[TD][/TD]
[TD]Moderate
[/TD]
[TD][/TD]
[TD]High
[/TD]
[/TR]
[TR]
[TD]High
[/TD]
[TD][/TD]
[TD]Low
[/TD]
[TD][/TD]
[TD]Moderate
[/TD]
[/TR]
[TR]
[TD]Moderate
[/TD]
[TD][/TD]
[TD]High
[/TD]
[TD][/TD]
[TD]Moderate
[/TD]
[/TR]
[TR]
[TD]Low
[/TD]
[TD][/TD]
[TD]High
[/TD]
[TD][/TD]
[TD]Low
[/TD]
[/TR]
[TR]
[TD]Moderate or Low
[/TD]
[TD][/TD]
[TD]Moderate
[/TD]
[TD][/TD]
[TD]Low
[/TD]
[/TR]
[TR]
[TD]Moderate or Low
[/TD]
[TD][/TD]
[TD]Low
[/TD]
[TD][/TD]
[TD]Low
[/TD]
[/TR]
[/TABLE]

Thank you!

I’d put the second table in slightly different format, that directly maps the Inherent Risk to the Control Risk, like so:
[TABLE=“class: grid, width: 192”]
[tr]
[td]↓IR\CR→[/td]
[td]H[/td]
[td]M[/td]
[td]L[/td]
[/tr]
[tr]
[td]H[/td]
[td]H[/td]
[td]H[/td]
[td]M[/td]
[/tr]
[tr]
[td]M[/td]
[td]M[/td]
[td]L[/td]
[td]L[/td]
[/tr]
[tr]
[td]L[/td]
[td]L[/td]
[td]L[/td]
[td]L[/td]
[/tr]
[/table]

Then you can perform a 2-dimensional lookup with the following formula to get the Assessed Risk of Material Misstatement:
=VLOOKUP(A2,Sheet2!\$A\$1:\$D\$4,MATCH(B2,Sheet2!\$B\$1:\$D\$1)+1,FALSE)

See http://technet.microsoft.com/en-us/library/cc750573.aspx for a step-by-step explanation how the above formula works.

Thanks for the help anymunym! It works so far except for the two highlighted below. H/L gives me H and M/L gives me M. It’s possible that L/L is wrong too. because the results are only referencing the first criteria, but it happens to be the correct answer. I may have just entered the formula wrong, but can’t troubleshoot until after work. The “table array” is highlighting the entire bottom three rows, and the “match array” is just highlight the H/M/L in the first row.

[TABLE=“class: grid, width: 192”]
[TR]
[TD=“width: 64, bgcolor: white”]↓IR\CR→
[/TD]
[TD=“width: 64, bgcolor: white”]H
[/TD]
[TD=“width: 64, bgcolor: white”]M
[/TD]
[TD=“width: 64, bgcolor: white”]L
[/TD]
[/TR]
[TR]
[TD=“width: 64, bgcolor: white”]H
[/TD]
[TD=“width: 64, bgcolor: transparent”]H
[/TD]
[TD=“width: 64, bgcolor: transparent”]H
[/TD]
[TD=“width: 64, bgcolor: yellow”]M
[/TD]
[/TR]
[TR]
[TD=“width: 64, bgcolor: white”]M
[/TD]
[TD=“width: 64, bgcolor: transparent”]M
[/TD]
[TD=“width: 64, bgcolor: transparent”]L
[/TD]
[TD=“width: 64, bgcolor: yellow”]L
[/TD]
[/TR]
[TR]
[TD=“width: 64, bgcolor: white”]L
[/TD]
[TD=“width: 64, bgcolor: white”]L
[/TD]
[TD=“width: 64, bgcolor: white”]L
[/TD]
[TD=“width: 64, bgcolor: white”]L
[/TD]
[/TR]
[/TABLE]

MATCH(B2,Sheet2!\$B\$1:\$D\$1)
should be
MATCH(B2,Sheet2!\$B\$1:\$D\$1,0)

The latter requests an exact match (or error if not found). The former assumes the search is sorted ascending, and if it passes by the requested object, it returns the one before it (so in H/M/L, since M > L, it stops searching and returns the location of H).

You can diagnose this stuff by either using the evaluate formula function, or by breaking things up into multiple steps.

An alternate way that uses only one lookup - combine CR and LR into one cell, eg/
[TABLE=“class: grid, width: 327”]
[TR]
[TD]HighHigh[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD]HighModerate[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD]HighLow[/TD]
[TD]Moderate[/TD]
[/TR]
[TR]
[TD]ModerateHigh[/TD]
[TD]Moderate[/TD]
[/TR]
[TR]
[TD]ModerateModerate[/TD]
[TD]Low[/TD]
[/TR]
[TR]
[TD]ModerateLow[/TD]
[TD]Low[/TD]
[/TR]
[TR]
[TD]LowHigh[/TD]
[TD]Low[/TD]
[/TR]
[TR]
[TD]LowModerate[/TD]
[TD]Low[/TD]
[/TR]
[TR]
[TD]LowLow[/TD]
[TD]Low[/TD]
[/TR]
[/TABLE]

And then
=VLOOKUP(A2&B2,Sheet2!A:B,2,FALSE)

That did it fdsaion! Thanks guys, I wouldn’t have figured it out myself!