Forum Discussion
Array partial matching with VLOOKUP
Forgive me for my inability to wrap my head around all the conditions you're testing for here. I do this purely as a volunteer, and sometimes will spend quite a bit of time trying to get through a quandary such as yours.
What I've read, though--convinces me that you may just need to be pointed in a different direction, since you clearly have a fair amount of Excel ability already. Are you aware of the new Dynamic Array functions? Notably FILTER?
FILTER, like VLOOKUP, can search through a table and find matches....unlike VLOOKUP--this is where it gets exciting--FILTER can combine multiple criteria and find multiple matches, yielding a list of the rows that meet said multiple criteria.
It seems to me that using FILTER, with criteria working to yield a separate list of the rows that match, you can just produce that list on a sheet of its own, dynamically (as conditions change) and forget Conditional Formatting. Take a look at this video (you will need the most recent version of Excel for these Dynamic Array functions to work): https://www.youtube.com/watch?v=9I9DtFOVPIg
Thanks so much for the reply. I've been learning the beast that is excel at my own pace out of necessity and until now I'd never heard of the FILTER function. It'll take some tinkering on my end to get to know it but that's all I was asking for; a nudge in the right direction.