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]

What do you want the formula return? for example, when the 2 criterias are met, return value from column E?

 

[shaowu459]

=IFERROR("2 criterias are met in sheet "&INDEX(A1:D1,MATCH(1=1,COUNTIFS(INDIRECT(A1:D1&"!D:D"),A2,INDIRECT(A1:D1&"!A:A"),A3)>0,)),"")

Ctrl+Shift+Enter for the arrayformla, this formula will return in which sheets the two criterias are met.

 

[forginganewone]

=IFERROR("2 criterias are met in sheet "&INDEX(A1:D1,MATCH(1=1,COUNTIFS(INDIRECT(A1:D1&"!D:D"),A2,INDIRECT(A1:D1&"!A:A"),A3)>0,)),"")

Ctrl+Shift+Enter for the arrayformla, this formula will return in which sheets the two criterias are met.

I want it to again give me the sheet name in which both criteria are met.

For e.g there are 4 sheet names in A1,B1,C1,D1 in which there is a possibility of data. After the new formula it will search for 2 criteria and give 1 sheet name with both criteria met.

 

[forginganewone]

=IFERROR("2 criterias are met in sheet "&INDEX(A1:D1,MATCH(1=1,COUNTIFS(INDIRECT(A1:D1&"!D:D"),A2,INDIRECT(A1:D1&"!A:A"),A3)>0,)),"")

Ctrl+Shift+Enter for the arrayformla, this formula will return in which sheets the two criterias are met.

Worked like a charm. Thank you sir.
Maybe you can help me skip a step.

Here is an array formula that provides sheetnames in A1,B1,C1,D2 :
=IFERROR(INDEX(Sheetlist,1/(1/SMALL(IF(ISNUMBER(1/COUNTIFS(INDIRECT("'"&Sheetlist&"'!D:D"),$A41)),
ROW(INDIRECT("1:"&COUNTIFS(Sheetlist,"?*")))),COLUMNS($G40:G40)))),"")

I create sheet names by putting sheets names and giving all of them name :Sheetlist. After that the formula search for criteria from column $Acell and search it in column D of every sheet and returns back values, i drag the forumla is 4 columns in same row so it doesn't duplicate the sheet name.

Maybe you can amend the formula and make it search for criteria A1 and D1 in the list name :sheetlist.
Search A1 criteria in column A of every sheet and D1 criteria in column D of every sheet.

 

[shaowu459]

Sorry, i dom't quite understand what you formula do without a workbook. Hope someone else can help you.

 

[forginganewone]

Worked like a charm. Thank you sir.
Maybe you can help me skip a step.

Here is an array formula that provides sheetnames in A1,B1,C1,D2 :
=IFERROR(INDEX(Sheetlist,1/(1/SMALL(IF(ISNUMBER(1/COUNTIFS(INDIRECT("'"&Sheetlist&"'!D:D"),$A41)),
ROW(INDIRECT("1:"&COUNTIFS(Sheetlist,"?*")))),COLUMNS($G40:G40)))),"")

I create sheet names by putting sheets names and giving all of them name :Sheetlist. After that the formula search for criteria from column $Acell and search it in column D of every sheet and returns back values, i drag the forumla is 4 columns in same row so it doesn't duplicate the sheet name.

Maybe you can amend the formula and make it search for criteria A1 and D1 in the list name :sheetlist.
Search A1 criteria in column A of every sheet and D1 criteria in column D of every sheet.

Amended the formula to work, i am almost there : =IFERROR(INDEX(sheets,MATCH(1=1,COUNTIFS(INDIRECT(sheets&"!D:D"),A2,INDIRECT(sheets&"!A:A"),A3)>0,)),"")

can you amend it further to add it so that if i drag the formula in other columns it will give multiple sheets names if the criteria appear in multiple sheets without duplicating the sheet name. My previous formula used to do that, you can see it in previous post.

 

[shaowu459]

you mean there may be more than one worksheets which the 2 criterias are met, and you want to list out all the sheets' name? Hope you can understand my english....

 

[shaowu459]

Try this:

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

replace A1:D1 with sheetsname you defined, replace column(A1:D1) with transpose(row(indirect("1:"&counta(sheetaname)))), drag formula to the right, you will get all sheetname that the 2 criterias are met.

 

[forginganewone]

Try this:

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

replace A1:D1 with sheetsname you defined, replace column(A1:D1) with transpose(row(indirect("1:"&counta(sheetaname)))), drag formula to the right, you will get all sheetname that the 2 criterias are met.

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.