How to apply arrayformula to a series of columns

0
votes

I'm trying to make a spreadsheet to track membership for an organization.

Basically my design is an input sheet with columns of names associated with expiration dates, then another sheet that collects all the unique names and all of their associated expiration dates, and then one last sheet that filters the names into only those with expiration dates in the future.

I am able to collect all the unique names into one column using an arrayformula, but I am stuck trying to do a lookup operation of some kind that, for each name, will look for the name in each column and if it appears then it will add the associated expiration date to it's list (and otherwise add a blank cell, and then I can filter out the blank cells).

Is there a way to use vlookup or anything else in an arrayformula to do a series of operations for all columns in a range? Also, I want to use arrayformula because I want the formula to be infinite so the spreadsheet can keep growing. I've tried using

=ARRAYFORMULA(IF(ISERROR(VLOOKUP(A1:A,Sheet1!A2:200,1,FALSE)),,Sheet1!A1:1))

But vlookup, and anything else I tried like match, interprets Sheet1!A2:200 as a single range and performs a lookup only in the first column and does not do a separate lookup in each column.

For example, I might have this input on Sheet1

enter image description here

And want this result on another sheet

enter image description here

1
google-sheets

1 Answers

0
votes

I suspect the combination of what you would really like and what is reasonably practical is a script but the following is an array formula, though would be cumbersome to extend and does require copying down (from B1):

=split(if(ISERROR(match(A1,Sheet1!A:A,0)),"",Sheet1!A$1)&"|"&if(ISERROR(match(A1,Sheet1!B:B,0)),"",Sheet1!B$1)&"|"&if(ISERROR(match(A1,Sheet1!C:C,0)),"",Sheet1!C$1),"|")

Assumes a unique list of names in ColumnA, such as created by:

=unique(QUERY({Sheet1!A2:A6;Sheet1!B2:B6;Sheet1!C2:C6},"where Col1 is not NULL"))

in A1.