Search multiple words in Microsoft Excel

[gadgetwiz]

Hi. This has given me a headache since yesterday when one of my suppliers required to use their own order from that I have to search an SKU number from the list of 1000 items in several worksheets.


I need to set up a quick way to search multiple words in Microsoft Excel to make this frustrating situation a bit easy. For example, I need to find a row that contains "iphone 4 crystal red" to find a product SKU for that item. I can go to a specific worksheet, so it doesn't have to be a cross-worksheets.

What would be your best suggestion? I can modify VBA code if I get an instruction, but not very familiar with that. I would really appreciate your help. Thank you.

 

[SkipVought]

hi,

So you need a reference table that relates the ProductName and the SKU code.

Then you can create a lookup formula to return the SKU for a given ProductName: no VBA requred, perhaps.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

I would suggest making an array to hold the names you want and then do a loop through the list in the array.

To get the find code, use the macro recorder and edit from there.

If you aren't comfortable with arrays then have the code scan a list of the words and then use the find code from there.

 

[gadgetwiz]

Hi, Skip. Thanks for your reply. There are couple problems, though.

1. Actual product name is painfully longer. For example, the actual name of the one that I wrote could be "ABC iphone 4 ultra crystal clear front screen protector set" something like this..

2. There is already the reference table with SKU number on the left and product name on the right (they made this order sheet for users to enter SKU, and the product name/price pops up with Vlookup function.) So, I don't want to change the structure of the reference table to solve my own problem-it's going to be too much complicated because the item list is updated daily.

Thanks.

 

[SkipVought]

Please post a sample of the table you refer to and a related product name, that illustrates the problem.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gadgetwiz]

OK, for example, a row in a worksheet(product_list) to create reference table(SKUprice) looks like this:

Camera ABC00001 ABC SONY a55 Front Screen Protector [Crystal Clear] $1.99

The reference table, "SKUprice" has SKU on the far left, product name, and price. (ABC00001 is SKU, and "ABC SONY..." is product name, and $1.99 is the price.)

This reference table is used to popup product name in order sheet.

The order sheet uses vlookup function. For example, I copy and paste SKU code, then product name field has: =+VLOOKUP(E15,SKUprice,2,FALSE)

The problem for me is, I should be able to find SKU easily by searching multiple words to find exact (or similar) matching record. Find feature in Excel works for only one word and cannot find multiple words. Thanks.

 

[SkipVought]

Excel WILL find [highlight]ABC SONY[/highlight] in a cell containing [highlight]ABC SONY a55 Front Screen Protector [Crystal Clear][/highlight] using the FIND feature on the sheet.

You will have to use the Find method with xlPart in VBA code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gadgetwiz]

Ok, I don't 100% understand this part: "Find method with xlPart in VBA code."

What I meant by "Find feature in Excel" was the find menu. I didn't know about Find function.

I quickly tried this, but got a Value error: =FIND("iphone 4 crystal screen",C2:C10000)

Could you let me know how to use it? This will save tons of my time as well as others-I'm concurrently working on the order sheet while checking on this board

Thanks!

 

[SkipVought]

Turn on your macro recorder and record using the Find Feature.

Post back with your recorded code to get help developing a VBA solution.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gadgetwiz]

Hi, Skip. Sorry, my hands are full with some other stuff to finish by today. I'll post it as soon as I can. thank you so much for your help.