# Three way lookup using Match and Index

#### stevo239

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

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
=offset(startcell,match("moderate",b2:b7,0)+1,match("flood", c1:c4,0)+2)

suggest startcell is A1 NOT TESTED

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

suggest startcell is A1 NOT TESTED

Thanks very much!!!

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

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.

my c1 to c4 should have read C1 to G1

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

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.

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

Replies
5
Views
220
Replies
10
Views
686
Replies
5
Views
2K
Replies
2
Views
1K
Replies
2
Views
981

1,196,428
Messages
6,015,191
Members
441,882
Latest member
rcgyuk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back