Vlookup based on 2 conditions through multiple sheets?

The array formula won't search through all of the spreadsheets in your list. The best way I could do it was with helper columns and using this formula:

=SUMIFS(INDIRECT(D$1&"$C:$C"),INDIRECT(D$1&"$A:$A"),$A2,INDIRECT(D$1&"$B:$B"),$B2)

I tried an array formula using index and match with 2 criteria, but it took so much processing power that it wasn't worth it. Luckily you are searching for a number and in this case you can just sum the numbers found.

Your current solution is pretty amazing for looking up all of the sheets based on 1 criteria. I will have to save it and try it in other places.

I think this array formula may work in C2. I used your array formula and just changed the countif to a countifs so I could add multiple criteria. I also changed the Cell references on the first tab so they only reference the tab. That way I can control what column it is looking at in the countifs section.

=IFERROR(VLOOKUP(A2,INDIRECT(INDEX(Sheets&"$A:$C",MATCH(1,COUNTIFS(INDIRECT(Sheets&"$A:$A"),A2,INDIRECT(Sheets&"$B:$B"),B2),0))),3,0),"N/A")

It looks like you got the original formula from here: http://www.myonlinetraininghub.com/e...ultiple-sheets

High five to Mynda Treacy who created it. It is quite an amazing feat.

I would add all data in 1 sheet (with a macro).

After that a pviot table to analyze the data.

I gave an example in the sheet.

See the attached file.

@Kelly. Right now your list on the first tab has the name of the sheet and the cells to look at. Like this:

'DAILYBBPT'!A1:C4000

If you change it to just the sheet name like this:

'DAILYBBPT'!

Then my formula will use that sheet name and add in the cell references.

=VLOOKUP(A2,INDIRECT(INDEX(Sheets&"$A$1:$C$1000",MATCH(1,COUNTIFS(INDIRECT(Sheets&"$A$1:$A$1000"),A2,INDIRECT(Sheets&"$B$1:$B$1000"),B2),0))),3,0)

It is pretty intersting to do all of this in one cell. If possible I would still recommend the helper colums like in my first example. They make it easier to trouble shoot and add new sheets. It is also easier for someone else to understand what is going on.