Hi all,



Hoping you can help, I need to build a search engine into my spreadsheet. I have attached the tables below for you to get an idea what I am after.

I need to be able to fill in the top boxes with some or all information and it return a list below it based on those criteria and taking it from the table of information.

Hope that makes sense.

Cheers

Mac

Attached Files

Book1.xlsx (9.9 KB, 13 views)

It is not obvious to me what you want.
In your example (with H5 = Orange) - what if I5 = Juice or K5 = Apple or L5 = 6 etc. etc?

Please give more examples of expected results.

Hi,

The idea is that the person searching might not know all 5 items so they can put in some info and it will return a list with anything that is close to it but still based on the items they do know. I have attached another sheet to look at, at the top is where the user would type what they are looking for below that is what I want it to return and the last table is where the information is pulled from.

I have done 6 Examples using the same data but with different search criteria each time so you can see what the results should be.

Cheers

Mac

Attached Files

Book1.xlsx (16.2 KB, 10 views)

I regret, but I can't help you.

Thanks for having a go.

I looked at this and immediately thought macro to filter the data, but I see at MrE you specifically state without macros or VBA.
Mind if I ask why ?

when I send the file off via email I have multiple accounts that block any macro or vba based spreadsheets.

Can you do it without macro or vba?

OK, I can understand that, makes sense.

Sorry, a formula solution is beyond my ability.

Can you do it with macro/vba? I may have to find a new way of distributing it. At least it will work.

Cheers for your help

Yes, but this has nothing dynamic, uses hard coded ranges of the file you attached.

Attached Files

Macacidic_Book1.xlsm (19.2 KB, 10 views)

That works perfectly, now how do I do that on the actual spread sheet lol.

Very carefully

Can't really tell you without knowing the precise layout of things.
Can you mock up a workbook with sheets indicative of what you actually have re: location and sheet names ?

I tried to modify your code and kept getting debugged

I have uploaded a mock up of the workbook I am working on.

I have blacked out fields in the search engine as they will never be used to search so can be ignored.

The master dataset is where the information is coming from and will be 1000 rows.

Everything is in the right place.

Hope this is not too complicated.

Cheers for your help, really appreciated

Attached Files

Mock.xlsx (15.9 KB, 9 views)

Hi Mac

Think this works, hope I've commented the code well enough to follow.
Any issues let me know.

Good luck with the project.
NoSparks

Attached Files

Macacidic_Mock.xlsm (27.1 KB, 8 views)

Perfect, copied it over to my spread sheet and it works perfectly. Thank you so much, it is much appreciated.

Mac