Match 4 Worksheets & 1 flag cell to get respective values

Better if you can change the sheet name according B10 cell.

Then try

H2=IFERROR(VLOOKUP(B2,INDIRECT("'"&$B$10&"'!B:D"),3,0),"")

Copy down

With a Named Range (List - CTRL-F3 to view edit) and a list of sheets in L1 to L4, this array formula in H2, copied down:

=IFERROR(OFFSET(INDIRECT("'"&INDEX(List,MATCH(TRUE,COUNTIF(INDIRECT("'"&List&"'!B10"),1)>0,0))&"'!D2"),ROWS($1:1)-1,,,),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Originally Posted by Glenn Kennedy

You're welcome.

My actual Worksheet tab named are 11, 12, 13, 14 instead of Sheet1, Sheet2, Sheet3 & Sheet4
Instead of D2:D6 it is C2:C6 and instead of B10 it is D47

I had re-phrased your formula as
=IFERROR(OFFSET(INDIRECT("'"&INDEX(List,MATCH(TRUE,COUNTIF(INDIRECT("'"&List&"'!D47"),1)>0,0))&"'!C2"),ROWS($1:1)-1,,,),"")

but not getting the result. Where am I making mistake?

I have attached replica of the original file. Unable to get the results in AA2:AA6 using your formula. Where am I making mistake?

plus

the formula should get corresponding values of column C from the ‘resultant’ Worksheet (‘resultant’ Worksheet: Worksheet in which D47 is found equal to 1) after Matching column A of Worksheet (tab named 14) with column A of the ‘resultant’ Worksheet.

Please help

You forgot to enter the formula as an array. See explanation at Post 4.

No problem!!!

You are right. it does not do that. In all your samples (including this one) the naems were in the same order in every sheet. There was no mention anywhere that they could be in a different order. Now they are. I have randomised them and matched them:

=IFERROR(INDEX(INDIRECT("'"&INDEX(List,MATCH(TRUE,COUNTIF(INDIRECT("'"&List&"'!D47"),1)>0,0))&"'!C2:C7"),MATCH(A2,INDIRECT("'"&INDEX(List,MATCH(TRUE,COUNTIF(INDIRECT("'"&List&"'!D47"),1)>0,0))&"'!A2:A7"),0)),"")

Don't forget to enter as an array!!

LoL. You're welcome!!