Returning a value from a table with 4 variables

[cbruss]

Hello Excel Gurus

You will find the table below:

Screenshot 2017-05-25 20.34.30.png

I would like have excel automatically return the value from the table upon giving the 4 following variables:

- Your need
- LOD
- Your building (easy, medium or difficult level)
- sqm (1-500sqm, 501-1000sqm or > 1000sqm)

Questions:

1) Is it feasible in excel? (not sure VLOOKUP, MATCH... could work with 4 variables)
2) If so, how to do it?

Many thanks for your help

All the best,
Cbruss

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[cbruss]

Hello Excel Gurus

Thank you John for your suggestion: it is my first time on the forum, sorry for not being used to it. Hence, I am reposting further to the suggestion from the moderator

You will find the excel table with explanation in attachment:

I would like have excel automatically return the value from the table upon giving the 4 following variables (example in the excel table attached):

- Category
- LOD
- Level
- Surface

Questions:

1) Is it feasible in excel? (not sure VLOOKUP, MATCH... could work with 4 variables)
2) If so, how to do it?

Many thanks for your help! If it's still unclear, let me know

All the best,
Cbruss

[JohnTopley]

Try

=INDEX($D$3:$L$17,MATCH($B$20,$A$3:$A$17,0)+MATCH("LOD " &$B$21,$C$3:$C$17,0)-1,MATCH($B$22,$D$1:$L$1,0)+MATCH($B$23,$D$2:$F$2,1)-1)

I made minor changes to your headings (giving lower limit of a range):

[cbruss]

Many thanks John!!!

It works just great

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[cbruss]

Hello John,

Hope you are well. Further to your great help on the previous formula, I come back to you as I had to add 15 columns in total and tried to tweak your formula given I added those columns but I am unable to tweak it properly: see the excel sheet in attachment. May I ask you how I need to update the MATCH formula(s) for these many columns so I get the right output value please? Many thanks in advance.

Best,
Cedric

[JohnTopley]

Try

=INDEX($C$3:$U$17,MATCH($B$20,$A$3:$A$17,0)+MATCH("LOD " &$B$21,$B$3:$B$17,0)-1,MATCH($B$22,$C$1:$U$1,0)+MATCH($B$23,$C$2:$J$2,1)-1)

[cbruss]

Thank you John, looks like it works. Best, Cedric

[JohnTopley]

Deleted by JT