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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Result[/TD]
[TD]Arson[/TD]
[TD]Crime[/TD]
[TD]Flood[/TD]
[TD]Subsidence[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Not Covered[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Negligible[/TD]
[TD]0.85[/TD]
[TD]0.85[/TD]
[TD]0.85[/TD]
[TD]0.85[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Low[/TD]
[TD]0.95[/TD]
[TD]0.95[/TD]
[TD]0.95[/TD]
[TD]0.95[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Moderate[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]High[/TD]
[TD]1.10[/TD]
[TD]1.10[/TD]
[TD]1.10[/TD]
[TD]1.10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Extreme[/TD]
[TD]1.15[/TD]
[TD]1.15[/TD]
[TD]1.15[/TD]
[TD]1.15[/TD]
[/TR]
</tbody>[/TABLE]

* 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.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Not Covered[/TD]
[TD]1.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Negligible[/TD]
[TD]0.85[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Low[/TD]
[TD]0.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Moderate[/TD]
[TD]1.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]High[/TD]
[TD]1.10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Extreme[/TD]
[TD]1.15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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