Two drop down lists to look-up correct value from table?

[raraavis619]

Hello all - thank you in advance for any help you can provide.

I'm trying to create a quoting tool where the user selects the labor requirements (normal, after hours, union) [B5] and room type [B15] from their respective drop down lists - the form then pulls the correct price from a table based on those values [Columns B, C, D].

I'm stuck - I've tried variations of vlookup, index/match - but I can't figure out how to pull the table data based on the two drop down criteria. Please let me know if there's any additional information needed. Any help would be greatly appreciated!

 

[tyija1995]

Hey there, I've quickly replicated some of this and come up with this, is this what you're looking for:

MappingDocument.xlsx
	A	B	C	D	E	F	G	H	I	J
2	Room Name	Room Price	Room Price - After Hrs	Room Price - Union				Job Setup Questionnaire
3
4	1-on-1 Pod - Team Pod 40"	2111.5	2312.09	2333.21				Labor Requirements
5	Work Café	3316.94	3632.05	3665.22				After Hours
6
7
8
9								Room Name	Room Count	Price Per Room
10								1-on-1 Pod - Team Pod 40"	1	2312.09
Setup

Cell Formulas
Range	Formula
J10	J10	=INDEX($A$4:$D$5,MATCH(H10,$A$4:$A$5,0),SWITCH($H$5,"Normal",2,"After hours",3,"Union",4))

 

[raraavis619]

This is perfect! I'm in awe - I've been fumbling around with this for way too long. Thank you so much for your help. Very greatly appreciated!

 

[tyija1995]

This is perfect! I'm in awe - I've been fumbling around with this for way too long. Thank you so much for your help. Very greatly appreciated!

No problem! It was just a case of... or should I say switch of having it match a value that wasn't exact, e.g. "Normal" being associated with"Room Price" - so the SWITCH function just translates the labor requirement drop down in to either 2,3 or 4 which correlates to the column number in the data set, then the row number is just matched based on the users room name drop down which again is located in the first column of the data set

 

[raraavis619]

That's a very helpful explanation of the process. I hadn't run into the SWITCH function in my research - that was the key! Thank you again and have a great day!