One criteria, multiple matches

**danielhliu** · 06-06-2008, 11:05 AM

Hello,

I am pretty new at using Excel, so thank you in advance for your help. I have two columns of data shown below.

Parts list Part number
xyzP987rty *P987*
rzyP632rty *P632*
xykP987rty *P345*
addP345rsa

I needed to find which part numbers exist on the parts list. Knowing that the parts list have the part numbers in the middle of a lot of other useless numbers I placed wildcards at the beginning and end of each part number. Then I used this formula:

vlookup(B2,A2:A5,1,False)

It returned with xyzP987rty, but I also need it to also return with xykP987rty. Is there any way for me to use one criteria, such as P987 and return with multiple results such as xyzP987rty and xykP987rty. Thanks again

**NBVC** · 06-06-2008, 11:22 AM

Say your data starts in A2 and you have the Parts to search for (with wildcards in place) beginning at B2...

In C2, enter formula:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER... you will see { } brackets appear around the formula.

Then copy across a few columns and down as far as you have parts to match...

the matches will appear horizontally.

Note: After you make adjustments to ranges in the formula... you must reconfirm with CSE keys before copying...

**danielhliu** · 06-06-2008, 12:45 PM

This works great- Thanks so much

**danielhliu** · 06-06-2008, 03:44 PM

This formula works great, but my spreadsheet is huge. 11000 entries long. Is there a way to speed it up- would it be beneficial to look into making a vba macro?

**NBVC** · 06-06-2008, 03:51 PM

Unfortunately, array formulas (or CSE formulas) tend to get less efficient and therefore slower and slower as your arrays grow....since each item in the array needs to be assessed....

There may be ways to do it with macros, that would be faster, but, again, unfortunately, I am not the one that can provide that, since I am no VBA master...

Hopefully someone can join in with a VBA solution...