3

Background I have a sheet I use for managing software installed in computer labs at our college. Column A contains a list of software titles. Row 1 contains all the lab names, and all the cells in between contain a status of whether or not the software is installed.

Master Sheet

I then have tabs for each building where end users can reference particular labs to see what software is available in each lab. My sheet in these tabs currently uses a simple filter function where I am manually referencing each column that corresponds with the lab name.

=filter(Master!$A$19:$A$202,Master!F$19:F$202<>"")

Question Instead of manually setting the column for each filter, I want to filter based on the column that matches the lab name on my master sheet in row 1.

Reference Sheets

Something like: Filter(Master!$A$19:$A$202, match column from lab name & where the same column <>"")

I apologize if what I am trying to accomplish isn't clear. I basically need to combine an HLOOKUP with FILTER I am thinking. Thank you in advance!

player0
  • 124,011
  • 12
  • 67
  • 124
veen1981
  • 33
  • 4

1 Answers1

2

try:

=FILTER({Master!A$19:B$202, INDIRECT("Master!"&
 ADDRESS(19,  MATCH(A1, Master!$1:$1, 0))&":"&
 ADDRESS(202, MATCH(A1, Master!$1:$1, 0))), Master!B$19:B$202}, 
 INDIRECT("Master!"&
 ADDRESS(19,  MATCH(A1, Master!$1:$1, 0))&":"&
 ADDRESS(202, MATCH(A1, Master!$1:$1, 0)))<>"")

0

spreadsheet demo

Community
  • 1
  • 1
player0
  • 124,011
  • 12
  • 67
  • 124