Excel function to search and return multiple items from list

[elf21]

I have categorized thousands of images in Excel by entering details of each image as a separate record with three fields per image: filename, description, and keywords. The keyword field consists of multiple words, separated by commas - but they are all in one cell (per image).

I would like to be able to perform a search (preferably in a separate sheet or even another workbook) by simply typing in one keyword and then having Excel automatically list all images containing that keyword.

If I use the MATCH or VLOOKUP function, it only retrieves the first image that contains that keyword. But what I would like is all the images that contain that keyword. What is the best way for me to go about this?

Thank you!

 

[GlennUK]

Why not use filtering?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[elf21]

Filtering works fine. But since this list is for a customer, I wanted something more elegant and user friendly, where user is prompted to enter a keyword and is then presented with a list of all related images. This all in a different sheet so all other fields and records are never shown.

As a matter of fact, since user knows very little of Excel, I'd like for the Excel interface to be practically transparent so it doesn't look like Excel at all. Can I set something like this up?

I'd also like to set up something similar to help user add more images and keywords.

Thank you.

 

[GlennUK]

In that case do Advanced Filtering, copying the results to an output sheet, controlled by macro. That would be elegant and user friendly.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[firefytr]

I think a little education on AutoFilter is in place here. It's better than trying to create another solution instead of using native functionality.

-----------
Regards,
Zack Barresse

 

[elf21]

I am quite familiar with AutoFilter and have used it extensively. However, I do not want any of the information to be visible to the user until after he enters a keyword to search for. At this point, I would like for Excel to generate some sort of user-friendly report with a list of photos matching his request.

 

[firefytr]

Well, you could keep a cell specific to user input and have a change_event to run on any change in that cell, checking it's value, and creating your report based on it's value(s). The procedure run could still use AutoFilter, but could also be invisible to the user.

Would you need help, or want, something like this? If so, please explain your basic structure of data to us so as we may be of more assistance to you.

Take care!

-----------
Regards,
Zack Barresse

 

[xlbo]

Being as a VBA solution will be required for this, please post in the VBA forum Forum707

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question