Looking up text value in a row and column in a reference table and returning a text value

kdevi

New Member
Joined
Feb 25, 2015
Messages
1
Hi,

This might be simplistic - apologies!
I have a Risk Factor reference table in 1 sheet:

↓Likelihood Consequence→
Major Moderate Minor
Likely High High Medium
Possible High Medium Low
Unlikely Medium Low Low

In another sheet I have the columns

Description Consequence Likelihood Risk Factor

I want to put a formula in the Risk factor field that will look at the value typed into the Likelihood (Likely, Possible or Unlikely) and the Consequence column (Major, Moderate or Minor) - look up the 'reference table' and return with either the high, medium or low value as appropriate.

I know I can do a long nested IF statement but is there a better way?

Thank you for your time!

Katie
 
Last edited:

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,298
Hi and welcome to the forum.

Use an Index - Match - Match formula

=INDEX(Lookup-Table, MATCH(Likelihood, Likelihood-Column, 0), MATCH(Consequence , Consequence-Row, 0))

=INDEX(Sheet2!B2:D4,MATCH(C2,Sheet2!A2:A4,0),MATCH(B2,Sheet2!B1:D1,0))
 

Watch MrExcel Video

Forum statistics

Threads
1,113,929
Messages
5,545,082
Members
410,652
Latest member
Zot
Top