How to index match with multiple different columns

[Hmill]

I have been using an index match to pull data from a query to one of my spreadsheets. At first I was just having my query pull one day at a time so it was pretty simple. Now, I am having the query pull data from March till present and I need to index match my spreadsheet with multiple different columns to get the right data. Is there a way to do this.

This what the query looks like:

This is how I'm organizing data in spreadsheet:

This was my formula to index match when I was doing it one day at a time:
For cell B2:
=IFNA(IF($A2=Query3!$C$2,INDEX(Query3!$A:$A,MATCH(T!$B$1:$G$1,Query3!$B:$B,)),B2),0)

 

[KRice]

That sounds good. It works fine in my workbook. For reference, here is the Query3 worksheet I set up:

MrExcel_20220626.xlsx
	A	B	C
1	Number Completed	Cal Lab	Date Completed
2	3	Dimen	3/16/2022
3	4	Health Physics	3/16/2022
4	4	MAA Electrical	3/16/2022
5	10	Physical	3/16/2022
6	3	Torque	3/16/2022
7	1	Dimen	3/17/2022
8	1	Health Physics	3/17/2022
9	2	MAA Electrical	3/17/2022
10	4	Physical	3/17/2022
11	9	Torque	3/17/2022
12	2	Dimen	3/18/2022
13	7	Electrical	3/18/2022
14	1	Health Physics	3/18/2022
15	2	MAA Electrical	3/18/2022
16	7	Physical	3/18/2022
Query3

...and the results on Sheet2:

MrExcel_20220626.xlsx
	A	B	C	D	E	F	G	H
1		Dimen	Electrical	Health Physics	MAA Electrical	Physical	Torque	Total
2	3/16/2022	3	0	4	4	10	3	24
3	3/17/2022	1	0	1	2	4	9	17
4	3/18/2022	2	7	1	2	7	0	19
5	3/19/2022	0	0	0	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
B2:G5	B2	=IFNA(INDEX(Query3!$A$2:$A$16,MATCH(1,(Query3!$B$2:$B$16=B$1)*(Query3!$C$2:$C$16=$A2),0)),0)
H2:H5	H2	=SUM(B2:G2)

 

[Hmill]

Yeah, that's bizarre. I don't know if the data being from a query is what's causing the issue or what. I don't see why it would though.

 

[KRice]

Depending on what is supplying the data, it is possible that some hidden characters are included. One quick check for this is to examine the length of text strings to see if the results make sense. For example, =LEN(Query3!B2) where we see "Dimen", we should expect a result of 5. If a different result is obtained, some non-printing characters may have been imported with the query, and those will need to be identified and cleaned. But you had a simpler version of this working before, correct? So it seems unlikely that this would be the issue.

 

[Hmill]

Hey, I figured out the issue! Because this is an array formula, it must be entered with control + shift + enter, except in Excel 365. So it works now

 

[KRice]

Oh...sorry. I completely neglected to mention that. Here is another idea that should not require entering as an array formula:

MrExcel_20220626.xlsx
	A	B	C	D	E	F	G	H
1		Dimen	Electrical	Health Physics	MAA Electrical	Physical	Torque	Total
2	3/16/2022	3	0	4	4	10	3	24
3	3/17/2022	1	0	1	2	4	9	17
4	3/18/2022	2	7	1	2	7	0	19
5	3/19/2022	0	0	0	0	0	0	0
6
7	3/16/2022	3	0	4	4	10	3	24
8	3/17/2022	1	0	1	2	4	9	17
9	3/18/2022	2	7	1	2	7	0	19
10	3/19/2022	0	0	0	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
B2:G5	B2	=IFNA(INDEX(Query3!$A$2:$A$16,MATCH(1,(Query3!$B$2:$B$16=B$1)*(Query3!$C$2:$C$16=$A2),0)),0)
H2:H5,H7:H10	H2	=SUM(B2:G2)
B7:G10	B7	=SUMPRODUCT(Query3!$A$2:$A$16,(Query3!$B$2:$B$16=B$1)*(Query3!$C$2:$C$16=$A2))

 

[Hmill]

Preciate it sir!

 

[KRice]

I’m happy to help.