V lookup Across Multiple Tabs and Return Multiple values if found on a tab

[VBA learner ITG]

hi all,

Need your guidance if possible on how to amend the below array formula to do a lookup all tabs listed below?


=IFERROR(INDEX('4.RANGING CONVENIENCE'!$B:$B,SMALL(IF(B$1='4.RANGING CONVENIENCE'!$A:$A,ROW('4.RANGING CONVENIENCE'!$A:$A)- MIN(ROW('4.RANGING CONVENIENCE'!$A:$A))+1,""), ROW()-2)),"")


Tabs that needs to be lookedup:

4.RANGING CONVENIENCE

5.RANGING FRESH

6.RANGING GROCERY & HOME

7.RANGING DRINKS

8.RANGING BAKERY SNACKING

<colgroup><col></colgroup><tbody>
</tbody>

I have tried also amending the above formula to a Named Range and removed the ref to a singlular worksheet but it doesnt work.

Any advice and guidance appreciated.

 

[Special-K99]

Usual reply on something like this would be nested IFERRORS, e.g.

IFERROR(INDEX(...SMALL..),IFERROR(INDEX(...SMALL..),IFERROR(INDEX(...SMALL..),IFERROR(INDEX(...SMALL..),IFERROR(INDEX(...SMALL..),IFERROR(INDEX(...SMALL..))))))

And if it's an array formula change that A:A, B:B to something more sensible to reflect the maximum data you're likely to have, e.g. A1:A100000 B1:B100000
As it stands it will ne slow returning a result, and if you're having to nest IFERRORS it'll be even worse.