Forum Discussion
How to search multiple names across entire excel workbook and return data location data.
=DROP(REDUCE("",C3:C8,LAMBDA(q,r,VSTACK(q,DROP(REDUCE("",{"Tabelle1","Tabelle2","Tabelle3"},LAMBDA(u,v,HSTACK(u,v,TEXTJOIN(" | ",,IFERROR(TOCOL(IFS(INDIRECT(ADDRESS(1,1,1,,v)):INDIRECT(ADDRESS(11,10,1,,v))=r," row #"&ROW(INDIRECT(ADDRESS(1,1,1,,v)):INDIRECT(ADDRESS(11,10,1,,v)))&" col #"&COLUMN(INDIRECT(ADDRESS(1,1,1,,v)):INDIRECT(ADDRESS(11,10,1,,v)))),2),"nil"))))),,1)))),1)
This formula works in Excel for the web and Office 365 and returns the intended result in my sample file. It searches for a full match (data in a single cell). I restricted the search to range A1:J11 in each worksheet in my sample. If you wanted to search an entire worksheet that would mean 1048576 rows and 16384 columns (17.179.869.184 cells !!) which would Excel certainly cause to crash. However i'm sure it can be done for e.g. range A1:BA1000 without problems.