Looking for help with a formula that requires some lookup/match/index across tabs

[WD100]

Hi,

First thank you in advance for any and all help! I believe my challenge is that I am trying to match a number to a 2 way table and then pull pull a text string.

Tab A has a set of unique product numbers in Column C (For example purposes I only copied in a handful). Then Tabs B-F (tab B shown for example purposes) each have a unique set of "family codes" in Column B and the corresponding product numbers have bee identified to the right of the Family Code.

I am trying to write a formula in Column B of tab A that looks at Column C and then finds/matches it to the corresponding unique family code which was identified in Tabs B-F. For example, in Cell B2 of tab A, the formula would pull TC-1 and in cell B13 the formula would pull TC-3 and everything in-between.

If consolidating tabs B-F into one tab is needed I can do that as well.

Tab A

Tab B

 

[Domenic]

First, list your sheet names (Tabs B through F) in a range of cells, let's say G2:G6. Then, in Tab A, enter the following formula in B2, and copy down:

=LET(SheetName,INDEX($G$2:$G$6,MATCH(TRUE,COUNTIF(INDIRECT("'"&$G$2:$G$6&"'!C2:I5"),C2)>0,0)),INDEX(INDIRECT("'"&SheetName&"'!B2:B5"),MATCH(TRUE,COUNTIF(OFFSET(INDIRECT("'"&SheetName&"'!C2:I5"),ROW(INDIRECT("2:5"))-2,0,1),C2)>0,0)))

Change the references accordingly. If you need help with amending the formula, post back.

Hope this helps!

 

[WD100]

First, list your sheet names (Tabs B through F) in a range of cells, let's say G2:G6. Then, in Tab A, enter the following formula in B2, and copy down:

=LET(SheetName,INDEX($G$2:$G$6,MATCH(TRUE,COUNTIF(INDIRECT("'"&$G$2:$G$6&"'!C2:I5"),C2)>0,0)),INDEX(INDIRECT("'"&SheetName&"'!B2:B5"),MATCH(TRUE,COUNTIF(OFFSET(INDIRECT("'"&SheetName&"'!C2:I5"),ROW(INDIRECT("2:5"))-2,0,1),C2)>0,0)))

Change the references accordingly. If you need help with amending the formula, post back.

Hope this helps!

Amazing! Thank you!

 

[Domenic]

You're very welcome, cheers!