Forum Discussion
How to search multiple names across entire excel workbook and return data location data.
Hi I will like to find out how I can search for multiple data (listed in a column) across an entire workbook (either as a data in a single cell or even as the searched term is present as a word in a paragraph of details in a single cell) and return the cell position data (multiple, duplicate is alright as intention is to be comprehensive). An example as follow:
1a. Enter multiple search verbiage in a column
“abc co ltd”
“Def co ltd”
”ghi inc”
1b. Expected outcome for each input verbiage from above (1a)
“abc co ltd” worksheet 1, Cell a2, cell z5
“Def co ltd” nil
”ghi inc” worksheet 2 cell f5, worksheet 5 cell i4
thanks a million
1 Reply
=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.
