Excel Magic Trick 1169: Are Items In Row In Master List? Filter & Conditionally Format To Highlight

See how to Match Ingredients for a Drink with the data base for all drink ingregients:
1. (00:13 minute mark) Problem Setup
2. (00:35 minute mark) MATCH Function to Compare Two Lists: The Row as a Relative Reference and the Master List (Locked Absolute Range). This demonstrates a Function Argument Array Operation, and Array Formula that requires Ctrl + Shift + Enter.
3. (02:36 minute mark) Rap COUNT around MATCH to count how many Matches.
4. (03:10 minute mark) Use COUNTA to count how many items we are searching for and compare it to COUNT result using comparative Operator, Equal Sign
5. (03:40 minute mark) Add IF and COUNTA to check to see when no items have been entered in Master List.
6. (05:15 minute mark) Filter To Filter List
7. (05:40 minute mark) Conditionally Format the Row when a Match is Made.

[excelisfun]

Excel Magic Trick 1169: Are Items In Row In Master List? Filter & Conditionally Format To Highlight
See how to Match Ingredients for a Drink with the data base for all drink ingregients:
1. (00:13 minute mark) Problem Setup
2. (00:35 minute mark) MATCH Function to Compare Two Lists: The Row as a Relative Reference and the Master List (Locked Absolute Range). This demonstrates a Function Argument Array Operation, and Array Formula that requires Ctrl + Shift + Enter.
3. (02:36 minute mark) Rap COUNT around MATCH to count how many Matches.
4. (03:10 minute mark) Use COUNTA to count how many items we are searching for and compare it to COUNT result using comparative Operator, Equal Sign
5. (03:40 minute mark) Add IF and COUNTA to check to see when no items have been entered in Master List.
6. (05:15 minute mark) Filter To Filter List
7. (05:40 minute mark) Conditionally Format the Row when a Match is Made.

[OzduSoleilDATA]

BEAUTIFUL! Thanks for doing this, and building a full tool that includes the filtering and everything. This is good.

[aknoimak]

Super trick !!!
Thanks Mike. MATCH is very useful function.

[BillSzysz]

MATCH is one of my favorite functions :-)
Thanks for cool trick (COUNT, COUNTA)

[EricGiroux]

Thanks Mike, as usual extremely thoughtful!  By the way, I finally purchased ctrl+shit+enter, thanks, examples are very well detailed and explained.  Next book about powerpivot, powerquery? ;-)

[CalBeMe]

I really liked this tip but I still dont understand the logic for including the IF statement. The only time you need to use the lookup is when the "ingredients" are more than zero, so you have to check all the rows anyway. What am I missing?

[CalBeMe]

Does anyone know how this might this be modified to highlight the individual "ingredients" cells instead of entire rows?

[AdRok]

This was my thought to avoid Ctrl + Shift + Enter: =MAX(SUMPRODUCT(SUM(--(B10:F10=$A$2:$A$6))), 0)-COUNTIFS($B10:$F10, "")*COUNTIFS($A$2:$A$6, "")>=COUNTA($A$2:$A$6)