In Lieu of Filtering/Pivot Tables, What Formula Can Replicate Filtering?

Hi,
What I would like to do is write a formula that replaces me filtering the headers to determine which individual ONLY has a Ford (they cannot own any other car). Obviously, I could simply filter the headers to "Ford" and eliminate the rest of the Manufacturers but was wondering could I write a formula that basically does that in cells B13 - B18? I've attached the spreadsheet for reference. I'm assuming at a minimum, there has to be some sort of IF statement where any entries in Columns C-F are automatically eliminated. Thank you in advanced and apologies for the silly question.

Use cell H1 to specify which car you are interested in (e.g. Ford), then put this formula in H2:

=IFERROR(IF(INDEX($B$2:$F$7,0,MATCH(H$1,$B$1:$F$1,0))="Y",MAX(H$1:H1)+1,"-"),"-")

copy down to H7 (in your example). Then you can use this formula in B13:

=IF(ROWS($1:1)>MAX(H:H),"",INDEX($A$2:$A$7,MATCH(ROWS($1:1),$H$2:$H$7,0)))

Copy this down to B18. Then you can change the name of the car in H1 and see the list of names change accordingly.

Hope this helps.

Pete

You can make this change to the formula in H2:

=IFERROR(IF(AND(INDEX($B$2:$F$7,0,MATCH(H$1,$B$1:$F$1,0))="Y",COUNTIF(B2:F2,"Y")=1),MAX(H$1:H1)+1,"-"),"-")

(changes shown in red), then copy it down.

Hope this helps.

Pete

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Hello, @Pete_UK.

For some reason, the first AND argument in your formula in post #4 returns FALSE, so the formula returns "-".
So I gave it a little tweak:
=IFERROR(IF(AND(INDEX($B$2:$F$7,ROWS(A$1:A1),MATCH(H$1,$B$1:$F$1,0))="Y",COUNTIF(B2:F2,"Y")=1),MAX(H$1:H1)+1,"-"),"-")
Anyway, just for my information, I'd appreciate it if you would please tell me why, or what I may be doing wrong.

Thanks.

Maybe XL365 treates INDEX differently. You can also do it like this in H2:

=IFERROR(IF(AND(INDEX($B$2:$F$7,ROW(A2)-1,MATCH(H$1,$B$1:$F$1,0))="Y",COUNTIF(B2:F2,"Y")=1),MAX(H$1:H1)+1,"-"),"-")

which, it could be argued, is slightly easier to follow as the formula is on row 2.

Hope this helps.

Pete

Right. So I thoguht.
Thanks, Pete.