Finding partial match in databse with FILTER and XLOOPUP

Hi all, I'm running into an issue with a database I'm building.

The database contains for argument sake 10 people who all have multiple hobbies (seperate column) and interests (seperate column). Some have only 1 hobby, others have up to 4. Some hobbies overlap, some don't.

When looking for the hobby 'darts' I want to return the rows with all people that have either darts as their only hobby, or as one of their few hobbies. I believe I can do this through finding a partial match with COUNTIF - but I am failing. I have attached the worksheet where I'm able to do this based on exact matches, the formula I used for that is: =FILTER(Testsheet!J5:L8;(XLOOKUP(G$5;Testsheet!J4:L4;Testsheet!J5:L8)=H5))

partial match.PNG

If anyone could point me in the right direction I would be much obliged!

Thank you,

Aeth

Administrative Note:

Is your forum profile showing the Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

Sorry - I really don't understand what you are aiming for here. You don't appear to have mocked up your expected results anywhere. Also there are no 'people' - how can I tell whose hobbies are which?

And your Excel version? Obviously NOT 2003 any longer. Please update your profile.

Try this:

=LET(f,FILTER(F6:L9,F5:L5=B6),FILTER(E6:J9,ISNUMBER(FIND(C6,f))))

Why did you not show multiple criteria in the sample? Is there anything else you haven’t mentioned?

I’m offline now, but will look again tomorrow.

Are you using 365? That’s what needs to be in your profile.

What's the maximum number of criteria in each list? Is it four?

Also, is this an AND search or an OR search? Who would meet the criteria? There's no manual mock-up.

I need you to respond to my questions.

You green box suggests up to FOUR selections - is this to be allowed? Or will it be limited to one selection for hobbies and one for interests?

No response ...

So here's a guess (and I'll stop here until I get some feedback from you):

Please Login or Register  to view this content.

Goodmorning, sorry for the delay. I was rebuilding the entire sheet with what I am exactly trying to do, instead of using examples.

EDIT1: the filters should be AND - so only people that meet all requirements should show up. So in this case for example people that have all the hobbies that you filtered for, if you filter for a hobby that a person doesn't have - the person should not show up in the list.
EDIT 2: you will be able to filter for up to 5 hobbies, 5 interests, 5 lifestyles, 5 values. Right now I have not limited this yet, but I do know how to limit this for future use.

Please see attached, I will try your solution in the meantime.

Sometimes people over-simplify their sample workbooks.

Try this:

Please Login or Register  to view this content.

the filters should be AND

OK - then we might not be there yet, but AND was not what any of your previous posts seemed to be suggesting ...

PS I've spent a couple of hours on this now, so if there are further tweaks required, please provide a COMPREHENSIVE (i.e. multiple criteria selected, not just TWO) set of sample data with mocked up results.

I've just noticed that you have never marked any of your threads as SOLVED here. Once we are done here, I shall explain the forum's etiquette on this to you.

I'm curious to know why you've chosen to create a database in Excel and not Access (which is a database tool). Maybe you don't have Access, although I think it should be in the Enterprise version.

You don't need the fd parameter if it's not a choice option:

Please Login or Register  to view this content.

Glad to have helped.

If you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Learned to indeed better just share the 'real thing' instead of just an example.

The sample just needs to be REALISTIC! We would not want a sample with 100s of rows.

You, too. I'm not 100% convinced about your 'fix', so please test it thoroughly.