Google Sheets – Search Text in Multiple Columns Across Worksheets

formulasgoogle sheetsgoogle-sheets-arrayformulavlookup

I have 3 columns (Col1, Col2, Col3). All 3 columns are in their own worksheet (WS1, WS2, WS3).

I want to

  1. search each cell in Col1
  2. see if that cell matches anything within Col2 in WS2 then output "WS2"
  3. if nothing in Col2, then see if that cell matches anything within Col3 in WS3 then output "WS3"

Hope this is enough information

Best Answer

=ARRAYFORMULA(IFERROR(IFERROR(
 IF(VLOOKUP(A2:A, 'WS2'!A1:A, 1, 0)<>"", "WS2", ),
 IF(VLOOKUP(A2:A, 'WS3'!A1:A, 1, 0)<>"", "WS3", )), ))

0