Help with INDEX/MATCH, looking to MATCH Variables in 2 columns

[chrono2483]

Hello,

I'm trying to build an array formula that will return the value, based on matching criteria from 2 columns within that spreadsheet.

Here is an example of the data, and what I've pieced together so far that is returning an #N/A:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	2016-07-01	2016-07-03	2016-07-04	Dessert	Cakes	1368674	0	1368674	224401	1085457	83.6	202537	182253	1361	0	42148	$22.50
2	2016-07-01	2016-07-03	2016-07-04	Dessert	Pies	1241145	0	1241145	300185	837359	75.8	139070	104902	30937	18340	176943	$ 19.00
3	2016-07-01	2016-07-03	2016-07-04	Dessert	Cookies	3059556	568754	2490802	558392	1828159	77.6	297785	247521	84190	67283	243588	$ 15.75
4	2016-07-01	2016-07-03	2016-07-04	Dessert	Tarts	1917272	0	1917272	21638	1838426	98.9	19683	15126	0	0	6512	$ 2.50
5	2016-07-01	2016-07-03	2016-07-04	Dessert	Ice Cream	1857552	279807	1577745	541614	975525	65.7	447122	403595	83850	71052	66967	$ 5.00

<tbody>
</tbody>

{=INDEX('[Inventory.xlsx]Summary'!$C$2:$Q$5000,MATCH($A$1&$J$3,'[Inventory.xlsx]Summary'!$C$2:$C$5000&'[Inventory.xlsx]Summary'!$E$2:$E$5000,0))}

- where on my spreadsheet: A1 = today's date, and J3 = Cakes, I want the formula to return Q1 = $22.50


Is there something I am missing? Thanks!

 

[wideboydixon]

If you can guarantee at most 1 match in that table then:

=SUMPRODUCT(('[Inventory.xlsx]Summary'!$C$2:$C$5000=$A$1)*('[Inventory.xlsx]Summary'!$E$2:$E$5000=$J$3),'[Inventory.xlsx]Summary'!$Q$2:$Q$5000)

For your current formula, did you try changing the range for the INDEX to $Q$2:$Q$5000

WBD

 

[chrono2483]

If you can guarantee at most 1 match in that table then:

=SUMPRODUCT(('[Inventory.xlsx]Summary'!$C$2:$C$5000=$A$1)*('[Inventory.xlsx]Summary'!$E$2:$E$5000=$J$3),'[Inventory.xlsx]Summary'!$Q$2:$Q$5000)

For your current formula, did you try changing the range for the INDEX to $Q$2:$Q$5000

WBD

Thanks WBD - i tried to chance the range for the INDEX to $Q$2:$Q$5000 - but this still returns #N/A

The formula that you provide didn't seem to work, and returned 0; both the Date in column C + the Item in column E must match, to return the value in Q.

 

[chrono2483]

	A	B	C	D	E	F
1	2016-07-01	2016-07-03	2016-07-04	Cakes	224401	$22.50
2	2016-07-01	2016-07-03	2016-07-04	Cookies	300185	$ 15.75
3	2016-07-01	2016-07-03	2016-07-04	Tarts	558392	$ 2.50
4	2016-07-01	2016-07-03	2016-07-05	Pies	541614	$ 19.00

<tbody>
</tbody>

N1 = 05/07/2016
N3 = Pies
N4 = where the formula will go, to return $19.00

Knowing both N1 and N3 must match, what formula would work to return F4 ($19.00)?

(Similarly, if M3 = Cookies, M4 would return F2 = $15.75)

Hopefully that example simplifies the ask.

Thanks!!!

 

[63falcondude]

In N4 =INDEX(F:F,MATCH(N1&N3,C:C&D:D,0)) Ctrl Shift Enter

Make sure N1 is formatted correctly. I am assuming you meant July 5th 2016 and not May 7th 2016.