IF Query

[broncos347]

I have a table (see below) that I need to associate with another table.

BSMS Risk Log v1.0 130906.xls
	A	B	C	D	E	F	G	H
1	Likelihood	Certain		16	176	960	8800	16000
2		Likely		14	149	810	7425	13500
3		Moderate		10	105	570	5225	9500
4		Unlikely		6	61	330	3025	5500
5		Rare		3	28	150	1375	2500
6		Negligble		1	11	60	550	1000
7
8				VeryLow	Low	Medium	High	VeryHigh
9				Impact
Risk Assessment Matrix

On my other table I have two columns D and E where I can input the text shown on the above table. The text in column B above goes in to D and the text in row 8 goes in to column E. Depending on which word is in which column will determine what figure is shown in column F.

I am current using the folowing formula in column F:

=IF('Risk Log'!E5='Risk Assessment Matrix'!$D$8,INDEX('Risk Assessment Matrix'!$D$1:$D$6,MATCH(D5,'Risk Assessment Matrix'!$B$1:$B$6,0)))

But this only relates to the numbers in column D and the formula won't let me cover the whole table.

Is there anyone out there that can help?

 

[just_jon]

Guessing you're looking for -

=INDEX('Risk Assessment Matrix'!$D$1:$H$6,MATCH(D5,'Risk Assessment Matrix'!$B$1:$B$6,0),MATCH(E5,'Risk Assessment Matrix'!$D$8:$H$8,0))

 

[broncos347]

Excellent, works a treat. Many thanks.