Output Data based on 2 Data Validation Selections and Table Range

cristopulo

New Member
Joined
Aug 12, 2015
Messages
14
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!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,949
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Aug 12, 2015
Messages
14
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

Well-known Member
Joined
Aug 2, 2012
Messages
1,949
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,480
Messages
5,572,380
Members
412,460
Latest member
asmi_1758
Top