Index Match Functions with Ranged quantity lookups

[JMBurns578]

I know how to do index match and I know how to do vlookups to find a value within a quantity range. I cannot seem to get it to work doing an index match function pulling a value from a table within a range. See attached image.

I want to be able to type a quantity, any number 1-1000, into the qty cell M5 and have the price per unit pulled from the table on the left show up in cell N5.

 

[lrobbo314]

How about this.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2				1	101	251	501
3	Part Number	Supplier	Year	100	250	500	1000
4	123	Bob's	2020	50	48	45	43			Part Number	Supplier	Year	Qty	Price/Unit
5	123	Bob's	2021	51	49	46	44			123	Bob's	2021	150	51
6	123	Bob's	2022	52	50	47	45
7	456	Bob's	2020	2000	1981	1926	1885
8	456	Bob's	2021	2030	2010	1954	1913
9	456	Bob's	2022	2060	2040	1984	1941
10	789	Bob's	2020	526	522	518	500
11	789	Bob's	2021	533	529	525	507
12	789	Bob's	2022	541	537	533	515
13	123	Steve's	2020	47	45	42	40
14	123	Steve's	2021	47	46	46	41
15	123	Steve's	2022	48	47	44	42
16	456	Steve's	2020	1800	1782	1733	1696
17	456	Steve's	2021	1827	1809	1759	1721
18	456	Steve's	2022	1854	1836	1785	1747
19	789	Steve's	2020	573	568	564	545
20	789	Steve's	2021	581	577	573	553
21	789	Steve's	2022	590	586	581	561
Sheet1

Cell Formulas
Range	Formula
N5	N5	{=INDEX($D$4:$G$21,MATCH(J5&K5&L5,$A$4:$A$21&$B$4:$B$21&$C$4:$C$21,0),LOOKUP(M5,$D$3:$G$3,COLUMN($D$2:$G$2)-3))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[JMBurns578]

Thanks so much! That is working for most of it. For some reason when I change the qty to one of the values in the bottom range (1-100) I keep getting an #N/A. It works for exactly 100, but 1-99 it is #N/A.

Any thoughts on what is causing that?

 

[Fluff]

Maybe
=INDEX(D4:G21,MATCH(J5&"|"&K5&"|"&L5,INDEX(A4:A21&"|"&B4:B21&"|"&C4:C21,0)),MATCH(M5,D2:G2,1))

 

[DanteAmor]

Another way:
=SUMPRODUCT((A4:A21=J5)*(B4:B21=K5)*(C4:C21=L5)*INDEX(D4:G21,,MATCH(M5,D2:G2,1)))

 

[JMBurns578]

Thanks a ton!
That worked for my example excel. Now I just need to get it work for my actual data. Any ideas on why when I transfer the formula to a different excel and realign all the lookup cells I keep getting either #N/A or #REF or it pulls the wrong value?

 

[Fluff]

Depends on which formula you are referring to, how you changed it, where the data is & where the formula is.

 

[DanteAmor]

Hi @JMBurns578, welcome to the board!

The formula works with the following structure (Taking the examples captured by lrobbo314).
If the structure of your data is different you can put a sample here to adapt your formula.
Use the XL2BB tool.

Upload an excel range:
XL2BB - Excel Range to BBCode

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2				1	101	251	501
3	Part Number	Supplier	Year	100	250	500	1000
4	123	Bob's	2020	50	48	45	43			Part Number	Supplier	Year	Qty	Price/Unit
5	123	Bob's	2021	51	49	46	44			123	Bob's	2021	750	44
6	123	Bob's	2022	52	50	47	45
7	456	Bob's	2020	2000	1981	1926	1885
8	456	Bob's	2021	2030	2010	1954	1913
9	456	Bob's	2022	2060	2040	1984	1941
10	789	Bob's	2020	526	522	518	500
11	789	Bob's	2021	533	529	525	507
12	789	Bob's	2022	541	537	533	515
13	123	Steve's	2020	47	45	42	40
14	123	Steve's	2021	47	46	46	41
15	123	Steve's	2022	48	47	44	42
16	456	Steve's	2020	1800	1782	1733	1696
17	456	Steve's	2021	1827	1809	1759	1721
18	456	Steve's	2022	1854	1836	1785	1747
19	789	Steve's	2020	573	568	564	545
20	789	Steve's	2021	581	577	573	553
21	789	Steve's	2022	590	586	581	561
Sheet

Cell Formulas
Range	Formula
N5	N5	=SUMPRODUCT((A4:A21=J5)*(B4:B21=K5)*(C4:C21=L5)*INDEX(D4:G21,,MATCH(M5,D2:G2,1)))

 

[JMBurns578]

Thanks y'all!! I greatly appreciate the help. I got it working for the most part. My actual table has about 1,000 line in it. For some reason I can get the formulas to work for one section, about 250 lines, but then when I open it up to the full table of 1,000 lines errors start popping up. I imagine it has to do with my data.

Thanks again, this will help me out a ton!

 

[Fluff]

Glad we could help & thanks for the feedback