Combine table name with each value in the first column

Hello, I need a little help in Excel. So I have some tables of the following structure scattered across multiple sheets. (Fake data) Nokia Phone model Color 6610i Blue 3310 Y...

excel

Formulas and Functions

iliulian

Microsoft

Jul 17, 2025

Could this be expanded to handle cases where there are multiple tables in some sheets or none in some sheets without activating any advanced features, just with basic functions?

OliverScheurich

Gold Contributor

Jul 17, 2025

=LET(sheets,A1:A5,
sheetscombine,DROP(REDUCE("",sheets,LAMBDA(x,y,
VSTACK(x,IFERROR(TRIMRANGE(INDIRECT("'"&y&"'!A:B")),HSTACK("",""))))),1),
removeheaders,FILTER(sheetscombine,NOT(BYROW(INDEX(sheetscombine,,1),LAMBDA(x,ISNUMBER(SEARCH("Phone",x)))))),
filldownmanufacturer,SCAN("",MAP(INDEX(removeheaders,,1),INDEX(removeheaders,,2),
LAMBDA(a,b,IF(REGEXTEST(b,"[A-Za-z]+"),"",a))),LAMBDA(a,b,IF(REGEXTEST(b,"[A-Za-z]+"),b,a))),
stacked,HSTACK(filldownmanufacturer,removeheaders),
VSTACK(
HSTACK("Manufacturer","Model","Color"),
FILTER(stacked,REGEXTEST(INDEX(stacked,,3),"[A-Za-z]+"))))

I understand you only want basic functions but just in case you want to log on to your Microsoft account and use all the latest functions in Excel online you can use this formula. Or perhaps other users want to try this formula which handles multiple tables in some sheets and no table in some sheets.

Forum Discussion

Combine table name with each value in the first column

Resources