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

#### kdevi

##### New Member
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?

Katie

Last edited:

### 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
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))

Replies
8
Views
191
Replies
3
Views
322
Replies
12
Views
154
Replies
1
Views
169
Replies
1
Views
39