Vlookup,Hlookup, Data Validation and a Pricelist. Help!

[Margbot]

Hello,

I am hoping someone is able to help me on here. I am going to try to explain what I am trying to
do as best as possible. I have a pricelist that is based on the width and height of a product. Wherever the width and height meet is where the price is located. I am trying to come up with some sort of form on excel in which I can choose the height from one drop down list, the width from another, and have it give me the price (after it has looked for the intersecting cells from the spreadsheet). I have found various videos citing hlookup and vlookup but none that state if it is possible to use them together or how that would work. I am not an advanced user of excel so I am at a bit of a loss here.

Thank you so much for any help! I hope my explanation made sense.

 

[Smitty]

Welcome to the Board!

You can do what you want with INDEX/MATCH. You'll find a downloadable example here.

Or if you post a shot of your sheet (see the HTML Maker link in my sig), someone can set it up for you.

 

[Brian from Maui]

Instead of VLOOKUP, HLOOKUP you may want to consider INDEX/MATCH

=INDEX($B$2:$E$8,MATCH(F1,$A$2:$A$8,0),MATCH(F2,$B$1:$E$1,0))

Where B2:E8 is a matrix of prices that intersect the two criterias
A2:A8 house one criteria
B1:E1 houses the other criteria

 

[Margbot]

I hope the following link opens.
https://skydrive.live.com/redir?resid=66F5C013F9B74D12!129&authkey=!AA1U21c5EaWhD4U&v=3

Thank you so much!

 

[Smitty]

If you post a shot using the HTML Maker, then it can actually be copied right into Excel vs. an image which would have to be recreated.

 

[Margbot]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Height	24"	30"	36"	42"	48"	54"	60"	66"	72"	78"	84"	90"	96"
2	30"	19.89	23.8	29.58	32.81	38.08	40.8	47.09	49.64	51.34	55.76	60.69	63.24	65.62
3	36"	21.93	26.86	35.53	39.61	41.82	46.75	53.72	52.53	59.67	64.26	65.96	70.55	73.44
4	42"	25.67	31.62	39.27	44.03	48.11	52.02	59.84	61.37	67.66	72.93	74.46	80.07	83.64
5	48"	28.05	34	42.33	48.28	51.34	56.95	65.62	69.19	75.31	81.26	91.29	94.52	94.86
6	54"	30.77	36.55	44.2	50.15	53.55	372	71.06	77.18	496	91.29	102	105.06	105.4
7	60"	32.47	37.74	48.11	54.23	58.48	68.85	76.84	83.98	91.8	99.45	93.84	114.24	114.75
8	66"	35.19	41.14	51.68	58.65	64.26	75.48	84.49	91.63	101.15	109.48	121.38	125.12	125.46
9	72"	38.76	43.69	55.25	63.92	70.38	81.77	91.80	97.58	107.1	116.11	129.71	133.79	140.08
10	78"	40.63	47.77	56.61	65.96	72.08	85.34	94.52	100.64	114.41	124.27	139.06	143.31	143.82
11	84"	42.5	50.66	60.18	70.89	77.52	88.57	96.39	107.78	123.08	133.11	148.92	153.17	154.19
12	90"	45.73	53.89	63.92	75.48	82.11	93.50	102.00	114.58	130.39	141.44	158.61	163.54	164.9
13	96"	47.09	59.16	69.7	83.47	90.78	104.89	115.26	134.98	142.12	154.19	172.89	174.08	178.33
14	102"	49.64	63.07	75.14	88.4	96.56	111.86	122.23	137.02	151.13	163.54	183.94	185.47	186.15
15	108"	52.02	64.77	77.52	92.82	104.89	116.79	128.35	144.16	159.12	172.21	192.95	194.14	195.33
Sheet1

 

[Brian from Maui]

Try and extend the formula I posted

Instead of F1 and F2 use cells not in the matrix for your criteria

Aloha Smitty!

 

[Margbot]

I attempted what Brian suggested but I just kept getting an N/A even though I extended it.

 

[Margbot]

This is what I typed in: =INDEX($B$2:$N$15,MATCH(O1,$A$2:$A$15,0),MATCH(O2,$B$1:$N$1,0))

 

[Margbot]

Thank you guys! I got it! I was forgetting to link it to the cells where I had put the data validation. 5 stars for everyone!