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]

Since you have only one match for any set of conditions (date and cal lab), you can combine the two criteria in a MATCH function:

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]

That is not working, I'm just getting zeros for everything now.

 

[KRice]

Can you post the formula you are using…the $ signs are critical.

 

[Hmill]

I had just copied your formula in there directly.

=IFNA(INDEX(Query3!$A$2:$A$16,MATCH(1,(Query3!$B$2:$B$16=B$1)*(Query3!$C$2:$C$16=$A2),0)),0)

 

[KRice]

Let’s confirm a few things.
Is the main data sheet called Query3?
The counts in the data table are in Query3!$A$2:$A$16
The cal lab categories in the data table are in Query3!$B$2:$B$16?
The dates in the data table are in Query3!$C$2:$C$16?

The summary table uses heading names that exactly match the cal lab categories in the data table?...and those heading names appear in B$1:G$1?
The summary table uses dates that exactly match the dates in the data table?
Regarding these last two, go to a data table entry that you know should appear in the summary table and confirm that there are no extra characters (perhaps spaces or other non-printing characters)... alternatively, delete the category name in the main data table and re-type it and then do the same in the summary table.

 

[Hmill]

Yes it is called Query3.
Yes, except the data actually extends all the way to row 240. But the small sample size you're using should be fine.
I I
I I
Yes for the heading names question.
No for the date matching exactly. As you can see in the pictures I provided the date in the query includes a time with it. However, I noticed after plugging in your formula so I changed the dates in the query to be the same format and it still isn't working for some reason. As seen below:

 

[Hmill]

Can I email you the excel file or something so you can see the formatting yourself?

 

[KRice]

You can post the file to a file sharing site (Google Drive, Dropbox, Box, etc.) (also remove any sensitive information if necessary), then share the link here in a post. One point of clarification about pasting the formula...

I had just copied your formula in there directly

When the formula is copied and pasted, it would be pasted into the same cell (in this case B2 of the summary table) and then dragged, because as it is dragged, the formula references that are not fixed will adjust to point to the correct row and column headings.

About the time...The time format may or may not be an issue. I noticed that all times in your example were 0:00, which is interpreted as the midnight, the very beginning of a day. I assumed, perhaps incorrectly, that this was just a formatting issue and that you were not actually recording times on each day when the calibration was performed. The dates in my example all assume a time of midnight. If you have different times with your dates, then it would be an issue and the formula will need to be revised to deal with the time. You should be able to confirm that the dates will match by going to an empty cell somewhere and entering a logical test as shown here in J2 where I refer to a data table cell that contains the same apparent date. A TRUE result confirms that these are the same.

MrExcel_20220626.xlsx
	A	B	C	D	E	F	G	H	I	J
1		Dimen	Electrical	Health Physics	MAA Electrical	Physical	Torque	Total
2	3/16/2022	3	0	4	4	10	3	24		TRUE
Sheet2

Cell Formulas
Range	Formula
B2:G2	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	H2	=SUM(B2:G2)
J2	J2	=A2=Query3!C2

 

[Hmill]

Yessir, that's how I did it. I pasted it in B2 and dragged it to the other cells.
When I get home from work I will put it in a google drive folder and post the link in here. I can't access drive or any file sharing services from work. I don't know why it's not working, looking at the formula it seems like it would work to me.

I also tested to dates to see if they came back as the same and they did.