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:

[TABLE="width: 230"]
<colgroup><col></colgroup><tbody>[TR]
[TD]4.RANGING CONVENIENCE[/TD]
[/TR]
[TR]
[TD]5.RANGING FRESH[/TD]
[/TR]
[TR]
[TD]6.RANGING GROCERY & HOME[/TD]
[/TR]
[TR]
[TD]7.RANGING DRINKS[/TD]
[/TR]
[TR]
[TD]8.RANGING BAKERY SNACKING[/TD]
[/TR]
</tbody>[/TABLE]



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.