Need to nest indirect with index match

[forginganewone]

[FONT=&quot]Lets assume i have sheet names in cell A1,B1.C1.D1 and my required data can be in any of those sheets.[/FONT]
[FONT=&quot]What i want to do is make index match formula search on all 4 of those sheets on 2 criteria picking from A2 and A3.[/FONT]
[FONT=&quot]I can even tell that A2 criteria values will be in column D of those sheets and A3 criteria values will be in column A of those sheets.

P.S. The list sheet names in A1,B1,C1,D1 are already derived from an array formula.[/FONT]

 

[shaowu459]

So the formula is : =IFERROR(INDEX(sheets,SMALL(IF(COUNTIFS(INDIRECT(sheets&"!D:D"),$A2,INDIRECT(sheets&"!A:A"),$A3)>0,TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(sheetaname))))),COLUMN(A1))),"")

Its not working and also giving wrong value at the end.

the last sheetname should also replace with the name "sheets".

 

[forginganewone]

the last sheetname should also replace with the name "sheets".

Still not working. Here is the workbook : https://expirebox.com/download/c65edada2e66cf4fc2e9714e487d59de.html

the formula is on F7,G7,H7 cells of sheet6

 

[shaowu459]

I cann't access to your workbook. I send you a private message, you can send email to me.

 

[shaowu459]

F7=IFERROR(INDEX(sheets,SMALL(IF(COUNTIFS(INDIRECT(sheets&"!D:D"),$A2,INDIRECT(sheets&"!A:A"),$A3)>0,ROW(INDIRECT("1:"&COUNTA(sheets)))),COLUMN(A1))),"")

=IFERROR(INDEX(sheets,SMALL(IF(COUNTIFS(INDIRECT(sheets&"!D:D"),$A2,INDIRECT(sheets&"!A:A"),$A3)>0,ROW(INDIRECT("1:"&COUNTA(sheets)))),COLUMN(A1))),"")

Copy formula to G7:I7.

 

[forginganewone]

F7=IFERROR(INDEX(sheets,SMALL(IF(COUNTIFS(INDIRECT(sheets&"!D:D"),$A2,INDIRECT(sheets&"!A:A"),$A3)>0,ROW(INDIRECT("1:"&COUNTA(sheets)))),COLUMN(A1))),"")

=[B]IFERROR(INDEX[/B](sheets,SMALL([B]IF(COUNTIFS(INDIRECT(sheets&"!D:D"),$A2,INDIRECT(sheets&"!A:A"),$A3)>0[/B],ROW(INDIRECT("1:"&COUNTA(sheets)))),COLUMN(A1))),"")

Copy formula to G7:I7.

YES !!! THIS is exactly what i was looking for. You are a genius !
Thank you for taking time out to help me.

The Bold text in formula is the part which i understand, can you kindly tell me what other functions are doing so it will help me in future to amend it.

 

[shaowu459]

In your case COUNTA(sheets)=4 which means there are 4 values in "sheets". ROW(INDIRECT("1:"&COUNTA(sheets)))) returns row(1:4) and then returns {1;2;3;4}.

If COUNTIFS returns TRUE, which means the 2 criterias are met in such sheet, IF function will return the serial number of the sheet's name stored in G1:G4. For example, if sheet A and B met all the 2 criterias, IF function will return {1,2,FALSE,FALSE}. SMALL({1,2,FALSE,FALSE},column(A1))=SMALL({1,2,FALSE,FALSE},1) will return the smallest value 1. Copy formula to the right cell, SMALL({1,2,FALSE,FALSE},column(B1))=SMALL({1,2,FALSE,FALSE},2) will return the second smallest value 2. 1 or 2 are all the serial number of sheets' name stored in G1:G4 in case the 2 criterias are all met in those sheets. Finally, use INDEX gets the sheet's name.

English is a foreing language to me, hope you can understand above explanation.

 

[Aladin Akyurek]

Change the COLUMN(A1) bit to COLUMNS($A:A) if you are copying the formula columnwise for the sake of robustness.