Index and Match search engine with and without strings

**jm1245** · 05-28-2021, 09:47 AM

Hi,

I have an index and match search engine that I am trying to create and could use a bit of pro help.

I am using a helper column that contains a match/offset formula to go find my search criteria and return the row where its found.
I am pairing this with the index tool to then go and list the info found in Column A for each row that the search criteria is found.

The problem I have is that I have space delimited strings in some cells and others with no strings.

Because of this I can return info from a string using wildcards, or info without a string, but not both. I know that if I separate each line to remove the strings it would work but I really dont want to do that due to how this sheet will be used and due to who will be using it.

In the attached sheet the RPN search is where this problem can be seen. It returns 2 items when it should have 6.

If someone has time to take a look and offer some help I would certainly appreciate it.

**Fluff13** · 05-28-2021, 10:26 AM

Hi & welcome to the board.
You could get rid of the helper col & use this in N8

Formula:

Please Login or Register  to view this content.

**jm1245** · 05-28-2021, 10:50 AM

That works nicely to simplify the CPN search that is in that column.

I am primarily looking for a solution to the RPN search box but I appreciate the advice.

**Fluff13** · 05-28-2021, 10:51 AM

I am primarily looking for a solution to the RPN search box

Then why didn't you say that?

All you need to do is change the ranges.

**JohnTopley** · 05-28-2021, 10:54 AM

Use the same formula with changed ranges ???

=FILTER($A$1:$A$1000,ISNUMBER(FIND(" "&$P$7&" "," "&$E$1:$E$1000&" ")))

**jm1245** · 05-28-2021, 11:29 AM

Originally Posted by Fluff13

Then why didn't you say that?

All you need to do is change the ranges.

My apologies for not adding the most problematic cells in the sheet. I tried after you uploaded the fixed sheet but it does not return any values.

**Fluff13** · 05-28-2021, 11:32 AM

Can you post a new file showing what you tried.

**jm1245** · 05-28-2021, 11:35 AM

Originally Posted by JohnTopley

Use the same formula with changed ranges ???

=FILTER($A$1:$A$1000,ISNUMBER(FIND(" "&$P$7&" "," "&$E$1:$E$1000&" ")))

Thanks John for the updated formula information. Unfortunately I am unable to get anything to return. I believe its due to having some numbers with delimiters and some without in the same cell.

Question about that syntax though.....when you use " "&$P$7&" " do the " " location become wildcards on each end without an * symbol?

**jm1245** · 05-28-2021, 11:39 AM

Originally Posted by Fluff13

Can you post a new file showing what you tried.

Absolutely. I added it to the original named Tooling Search Engine with Filter.

**Fluff13** · 05-28-2021, 11:46 AM

Ok, thanks for that, just delete everything from P9 down & the formula in P8 will automatically spill down on it's own.

**jm1245** · 05-28-2021, 12:06 PM

Originally Posted by Fluff13

Ok, thanks for that, just delete everything from P9 down & the formula in P8 will automatically spill down on it's own.

Lol, wow I bet you get this often but I feel like a dummy for not doing that myself.

I appreciate you taking the time to look at this and find the correct solution to the problem.

**Fluff13** · 05-28-2021, 12:58 PM

You're welcome & thanks for the feedback.

Index and Match search engine with and without strings

LinkBack

Thread Tools

Rate This Thread

Display

Index and Match search engine with and without strings

Re: Index and Match search engine with and without strings

Re: Index and Match search engine with and without strings

Re: Index and Match search engine with and without strings

Re: Index and Match search engine with and without strings

Re: Index and Match search engine with and without strings

Re: Index and Match search engine with and without strings

Re: Index and Match search engine with and without strings

Re: Index and Match search engine with and without strings

Re: Index and Match search engine with and without strings

Re: Index and Match search engine with and without strings

Re: Index and Match search engine with and without strings

Thread Information

Users Browsing this Thread

Similar Threads

Index & Match Similar Strings

[SOLVED] Trying to convert mixed strings for lookup / index match.

Index / match Return Multiple Strings between Two Dates

Search for best match with strings

Index Match using text strings and dates

[SOLVED] INDEX MATCH based on part of strings

[SOLVED] Concatenated INDEX MATCH formula that won't give error that can index match 2-4 strings

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions