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

1. ## 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.

2. ## Re: In Lieu of Filtering/Pivot Tables, What Formula Can Replicate Filtering?

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

3. ## Re: In Lieu of Filtering/Pivot Tables, What Formula Can Replicate Filtering?

Hi Pete thanks for your help! I followed your instructions but was curious if i am doing something wrong as the results in B13-B18 should only show Dave, Mary, and Candace as I am seeking to only return value back for those who only have Ford (if they have other cars, then they would not count). Fred should not be listed since he doesn't only have Ford. I'm attaching an updated spreadsheet for reference and thank you again

4. ## Re: In Lieu of Filtering/Pivot Tables, What Formula Can Replicate Filtering?

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

5. ## Re: In Lieu of Filtering/Pivot Tables, What Formula Can Replicate Filtering?

Got it thanks Pete so much for your help!

6. ## Re: In Lieu of Filtering/Pivot Tables, What Formula Can Replicate Filtering?

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

7. ## Re: In Lieu of Filtering/Pivot Tables, What Formula Can Replicate Filtering?

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.

8. ## Re: In Lieu of Filtering/Pivot Tables, What Formula Can Replicate Filtering?

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

9. ## Re: In Lieu of Filtering/Pivot Tables, What Formula Can Replicate Filtering?

Right. So I thoguht.
Thanks, Pete.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1