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:

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


{=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]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2016-07-01
[/TD]
[TD]2016-07-03
[/TD]
[TD]2016-07-04
[/TD]
[TD]Cakes
[/TD]
[TD]224401
[/TD]
[TD]$22.50
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2016-07-01
[/TD]
[TD]2016-07-03
[/TD]
[TD]2016-07-04
[/TD]
[TD]Cookies
[/TD]
[TD]300185
[/TD]
[TD]$ 15.75
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2016-07-01
[/TD]
[TD]2016-07-03
[/TD]
[TD]2016-07-04
[/TD]
[TD]Tarts
[/TD]
[TD]558392
[/TD]
[TD]$ 2.50
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2016-07-01
[/TD]
[TD]2016-07-03
[/TD]
[TD]2016-07-05
[/TD]
[TD]Pies
[/TD]
[TD]541614
[/TD]
[TD]$ 19.00
[/TD]
[/TR]
</tbody>[/TABLE]

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.