Three way lookup using Match and Index

[stevo239]

I've done quite a bit of reserach on this but can't quite master the formula required for the following:

Location	Result	Arson	Crime	Flood	Subsidence
1	Not Covered	1.00	1.00	1.00	1.00
2	Negligible	0.85	0.85	0.85	0.85
3	Low	0.95	0.95	0.95	0.95
4	Moderate	1.00	1.00	1.00	1.00
5	High	1.10	1.10	1.10	1.10
	Extreme	1.15	1.15	1.15	1.15

<tbody>
</tbody>

* Assume Location is A1.

What is the formula if I want to Location 2, Result Moderate and Flood?

Thanks

 

[oldbrewer]

=offset(startcell,match("moderate",b2:b7,0)+1,match("flood", c1:c4,0)+2)

suggest startcell is A1 NOT TESTED

 

[stevo239]

=offset(startcell,match("moderate",b2:b7,0)+1,match("flood", c1:c4,0)+2)

suggest startcell is A1 NOT TESTED

Thanks very much!!!

 

[engineer69]

Try the SUMPRODUCT function. You can compare multiple items and return a result based on that.

 

[stevo239]

Try the SUMPRODUCT function. You can compare multiple items and return a result based on that.

How ow would the formula look if I wanted to variate the options from the lookup table as the original suggested formula is restricted to the options I suggested.

 

[oldbrewer]

my c1 to c4 should have read C1 to G1

you could make that C1:Z1 to allowfor more risks to be added

 

[stevo239]

Thanks for all the responses but it's not really doing what I need it to. I need to write a formula whereby if I select Location "2" (from a predetermined cell, say A10) and a Result of "Negligible" (from say A11) I should get a response of 0.85.

Location	Result
1	Not Covered	1.00
2	Negligible	0.85
3	Low	0.95
4	Moderate	1.00
5	High	1.10
	Extreme	1.15

<tbody>
</tbody>

The vlookup will work for the Result i.e. if Moderate then 1.00 but I can't seem to figure out how to bring in the Location criteria.

 

[oldbrewer]

unless the risk profile at each location is different, the location is irrelevant, you are finding negligible and stepping one column to the right - a standard vlookup

going back to your post 1 you find negligible and then step across to the risk type, a classic use of offset, match,match