I would like to search an entire sheet to find the cell that is an exact match for a cell in the current sheet
I have used the following to return the last cell in the 'Dropdowns' sheet containing the string in B1, but I need the match to be exact:
=ARRAYFORMULA(ADDRESS(LARGE(ISNUMBER(SEARCH(B1,Dropdowns!A:Z))*ROW(Dropdowns!A:Z),1),LARGE(ISNUMBER(SEARCH(B1,Dropdowns!A:Z))*COLUMN(Dropdowns!A:Z),1)))
Here is a link to my test Google sheet: https://docs.google.com/spreadsheets/d/1ZvGN9vYKT2qPgg5CFa-9Cgec2EDFH1bGXCTysTLLbWU/edit#gid=0
In this sheet you can see that 'Push Press' (B1) appears 3 times in the 'Dropdowns' sheet:
The formula above returns $H$53, the last instance of the string
Should I perhaps be using the query function instead?
Here's one approach you may test out:
=let(Σ,indirect("Dropdowns!1:"&rows(Dropdowns!A:A)),
tocol(makearray(rows(Σ),columns(Σ),lambda(r,c,if(index(Σ,r,c)=B1,address(r,c,4),))),1))