Ignoring blank cells in results

Copper Contributor

Hi All, I've attached a test sheet based upon our food bank worksheet. We have over 300 families in the rows and over a year of weekly food deliveries in the columns. I can generate results using IF, as on the test sheet (Ive also used vlookup and hlookup). With small numbers of rows and columns this is OK but what i need to do is produce reports of who was fed on a particular date - but without all the blank lines of families that were not fed and similarly for each family a list of the weeks they were fed with out all the blanks of weeks they were not fed. Thanks in advance.

6 Replies
Try this:

=FILTER($C$7:$C$13,OFFSET($C$7,0,XMATCH(F27,$D$6:$I$6,0),7,1)="Food")
Hi Patrick, thank you so much for your help. Your suggestion works perfectly on the columns, so I can get a report based on selecting a date. I have tried to modify your solution to search horizontally but failed misserably. What I would like to be able to do is request a list of dates that say family 'b' were fed. Please can you help me again. Thanks Simon.
Try this one:
=FILTER($D$6:$I$6,OFFSET($D$6,XMATCH(C29,$C$7:$C$13,0),0,1,6)="Food")

C29 is the cell containing the family letter.
Hi again Patrick, thanks so much again, this also worked perfectly on my little test page. For some reason when i rewrite both these on the actual sheet the lists produced are not correct, I've obviuosly made a mistake somewhere but cant see it at the moment!
Is it off by 1 row? Possibly a different arrangement with the header?
Again, THANKS! there were other columns with phone numbers etc. between the reference and the first date. Both reports working perfectly now. Thanks again!