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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,289
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,112,817
Messages
5,542,666
Members
410,567
Latest member
SCraig123
Top