google-sheetsgoogle-sheets-formula

How to extract multiple matched columns

I can extract matched columns using the FILTER function in Google Sheets, but I need help making it work when there are multiple matches. For example, if the header row contains the same value in several columns (like "Sales" in 3 places), I want to return all those columns dynamically — not just the first one.

I have attached a Google Sheets for you reference.

Google Sheet

Solution

  • Use filter() with a match() that lists the required column names, utilizing hstack(), like this:

    =filter('Raw Data'!A2:P, 
  match('Raw Data'!A1:P1, 
    hstack("Applicant ID", "Applicant Name", G1), 
    0
  )
)

    See filter(), match(), hstack() and your sample spreadsheet.