# 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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### 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
184
Replies
3
Views
316
Replies
12
Views
102
Replies
1
Views
114
Replies
1
Views
35

1,109,411
Messages
5,528,617
Members
409,828
Latest member
99DodgeRam

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...