Dynamic Filter Formula w/criteria list

[chrisTXACC]

Hello,

I am trying to learn and implement some new formulas.

Lets say I have a raw data table that contains transaction data with 25+ columns of unique transaction information. One of those columns list the customer name/ID. I have a separate table that is a list of all customers determining their tax exempt status (Yes or No). My goal is to in a separate worksheet, use the filter function spilling only the rows where the customer name/ID = "Yes". I am struggling to put this into a formula. If there is another thread that answers this questions, please forgive me as I couldn't locate it.


Thanks,
Chris

[RChad]

Please provide sample workbook it helps us to test formulas and have a better understanding of your specific requirements.

[Eastw00d]

Hi,
just worked this out from scratch (!).
you can use this formula:

Please Login or Register  to view this content.

Cheers
Erwin

[Gregb11]

Using the new dynamic array formulas, here's a different take on a solution. I have changed the file that was attached to show multiple records per customer because I assumed that's what you are expecting (as RChad says, it would be better if you attached a mocked up file with expected results - please read the yellow banner at the top of this page).

Anyway, the nice thing about the new dynamic array formulas is that you enter one formula in one cell and the whole result appears (no copying formulas down or across). the formula would be:

=FILTER(A2:C26,XLOOKUP(A2:A26,$E$2:$E$15,$F$2:$F$15,"Not found",0,1)="yes","Tax Exempt Status not found")

This is entered in cell H3 of the attached.

[Eastw00d]

@Gregb11,
these functions are not available in Office 2016, I see in front of the formula's : _xlfn._xlws

Cheers
Erwin

[Fluff13]

They're only available in 365 on the monthly channel.
But that is what the OP asked for, so suspect their profile is out of date.

[Gregb11]

Hmm, I thought for sure I saw the OP was on Office 365, but maybe I got confused because he was asking about the new formulas, which are only available in O365. Oh wait, I think the confusion is that this forum section is called "Office 365", so I would expect the answer to be based on O365.

[Eastw00d]

Yep, my bad, I've overlooked that.

Cheers
Erwin

[Fluff13]

Must admit I hadn't noticed this was in the Office 365 board.