Index/Match with 2-3 Matches or Criteria

[TreeLife]

Hi,

I've been trying to use a formula to bring the $value back based on Account Number and Date from Data on a different tab with no luck. Looking for a formula to this this correctly-Please see sample attached.

Thanks

[dosydos]

i suggest using a concatenate helper column in column A of your Data dump tab. concatenate date and item#
then use a vlookup or index match for month&item#

[FlameRetired]

Did you have something like this in mind? Try this formula in C2 filled down and across.

Please Login or Register  to view this content.

Row\Col	A	B	C	D	E	F
1	Item #	Item	January 2016	February 2016	March 2016	April 2016
2	94488	Art	$ 8,052.86	-	-	-
3	92199	Board	$ 14,616.13	-	-	-
4	92177	Chalk	$ 2,283.89	-	-	-
5	93188	Dates	$ 3,021.55	-	-	-
6	93118	Eraser	$ 3,021.55	-	-	-
7	94199	Art 2	-	$ 147.81	-	-
8	94299	Board 2	-	$ 61.30	-	-
9	94499	Chalk 2	-	$ 180.61	-	-
10	95199	Dates 2	-	$ 20.53	-	-
11	95399	Eraser 2	-	$ 20.53	-	-
12	96288	Art 3	-	-	$ 495.62	-
13	96588	Board 3	-	-	$ 201.73	-
14	97166	Chalk 3	-	-	$ 19.74	-
15	97420	Dates 3	-	-	$ 1,061.89	-
16	98660	Eraser 3	-	-	$ 13.71	-
17	98211	Art 4	-	-	$ 0.83	-
18	98311	Board 4	-	-	-	$ 0.83
19	98965	Chalk 4	-	-	-	$ 4.54
20	98611	Dates 4	-	-	-	$ 0.66
21	98711	Eraser 4	-	-	-	$ 0.31
22	98811	Art 4	-	-	-	$ 0.72
23	99188	Board 4	-	-	-	$ 39.39

[FlameRetired]

An alternative to concatenation. This is an array-entered formula

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[sanram]

Index/Match is not needed here, simply write this on C2.

Please Login or Register  to view this content.

Now copy right and down.

[sanram]

If you are interested to learn how to match multiple criteria, then try this array formula :

Please Login or Register  to view this content.

Confirm Ctrl+Shift+Enter

[TreeLife]

Both of these worked. Thank you!!! @FlameRetired and @Sanram, I used the Index/Match for this and was able to use the Sumifs on another file where I needed it.

Thank you All!

[sanram]

You are welcome and thanks for the rep.

[FlameRetired]

You are welcome. Thank you for the feedback and the rep.