Output Data based on 2 Data Validation Selections and Table Range

[cristopulo]

Hello,

I am creating a product selection tool for customers. In this selection tool they will be able to select options from different Data Validation lists. I want one option in the table to change based on 2 specific parameters that they have chosen and ultimately choose the right value from a list in a table on that page.

Ex: Using Data Validation they Choose Lamp Type and Contactor Type. Then the value is outputted from a table that lists different numerical values for a combination of the 2

I have tried to use this formula combining Index/ Match:

=INDEX($L$2:$O$17;MATCH(1;(B9=$K$2:$K$17)*(B15=$L$1:$O$1);0))

where L2:O17 is the range of possible values
B9 & B15 are where they choose their values (specific parameters)
and K2:K17 / L1:O1 are the headings of the table that match the chosen values

I input it as an array as well and just get an output error: #N/A

Is this the best way to go about completing this task and if so what have I Inputted incorrectly? Or would Hlookup/ Vlookup be a better solution?

Thanks in Advance!

 

[pjmorris]

Hi Cristo

Firstly, welcome to the forum.

You've nearly cracked it and the idea of using INDEX is entirely sensible. Using HLOOKUP or VLOOKUP would be possible.

You need the following formula: =INDEX(L2:O17,MATCH(B9,K2:K17,0),MATCH(B15,L1:O1,0))

You're nearly there, but perhaps you should have watched more closely as you entered the formula to see which element of each function you were describing - I think that would have helped you unravel the problem.

For info, if you wanted to use VLOOKUP you would need to have: =VLOOKUP(B9,K2:O17,MATCH(B15,L1:O1,0)+1,FALSE) not sure on what basis you would decide to use one or the other, though somehow I think the INDEX solution is more elegant.

Hope this helps.

Peter

 

[cristopulo]

Thanks a lot Peter! Worked perfectly.

Only thing I had to change were the commas to semicolons (don't know what that does in the end, maybe separates functions) and it worked.

Cheers!

 

[pjmorris]

Hi Cristo,

I think that that is location specific, I'm in UK and we use comma's to delineate function parameters. I believe other localities use comma's.

Glad I could help and thanks for the feedback.

Regards