excel search for a string in a table and return string if true

Question

Column A contains a varying text - around 1000 entries but the same text will appear in different cells.

In a second separate column (Column G) = a separate table, each cell contains one of a number of set text strings (each can be 2 to 5 characters long). Around 20 in all.

I want to search the start of each cell in column A for each of text strings in column G and if one is present (say G3's contents) I want G3's contents to be shown in column B in the same row as the matched cell from column A.

The matter is complicated by the text to be matched containing both ASC and SC and a further single charcater match being on * (although I can exclude * if this gives a simpler result).

Examples

Column A contains
ASC1 HHHH.........
ASC2 IIII.......
ASC2 AAAA.........
SC2 JJJJ....
TRIAL HOLE........
TRIALHOLE......
OP1 MMMM ...
ASC1COMPLEX
KAN1
PS BAN
*TTC Jiokl jj

Column G
ASC1
ASC2
*
OP1
PS
SC2
TRIAL
VERIFY

So column B should show
ASC1
ASC2
ASC2
SC2
TRIAL
TRIAL
OP1
ASC1
0 (or some other error/omit value)
PS
*

(How) can this be done? I have tried to adapt various solutions from the internet and failed.

Many thanks DaveT

barry houdini · Accepted Answer · 2013-11-07 16:27:01Z

3

Assuming G2:G20 contains the search strings - if you can list them in order of length (shortest first) so given your sample values the list would start with

* PS OP1 SC2 ASC1 ASC2 TRIAL VERIFY

then use this formula in B2 copied down

=LOOKUP(2^15,FIND(G$2:G$20,A2),G$2:G$20)

G2:G20 should be fully populated or adjust the range used - The matching is case-sensitive and #N/A is returned if there is no match

If you want to allow blanks in G2:G20 use this version

=LOOKUP(2^15,FIND(G$2:G$20,A2)/(G$2:G$20<>""),G$2:G$20)

edited Nov 7, 2013 at 16:27

answered Nov 7, 2013 at 15:12

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

rwking Over a year ago

Oh this is much simpler than mine. hahaha Nice, Barry.

rwking Over a year ago

This takes care of the error being returned: =IF(ISERROR(LOOKUP(2^15,FIND($G$1:$G$5,A1),$G$1:$G$5)), "", LOOKUP(2^15,FIND($G$1:$G$5,A1),$G$1:$G$5))

barry houdini Over a year ago

Thanks rwking - I thought from the question that an error would be acceptable when there was no match. In Excel 2007 or later you can use IFERROR like this: =IFERROR(LOOKUP(2^15,FIND(G$2:G$20,A2),G$2:G$20),"no match")

user2140261 Over a year ago

Using the samples provided by the OP this returns all 0's for me,

barry houdini Over a year ago

As I indicated in my answer the list of search terms (in column G) shouldn't include any blanks, so if you use just the 8 search items listed in G2:G9 you need to make the formula =LOOKUP(2^15,FIND(G$2:G$9,A2),G$2:G$9)....or you can have blanks in G2:G20 if you use this version =LOOKUP(2^15,FIND(G$2:G$20,A2)/(G$2:G$20<>""),G$2:G$20) - I'll update my answer.....

|

rwking · Accepted Answer · 2013-11-07 15:18:05Z

This works if you place an asterisk at the end of each string in Column G. The asterisk is a wildcard which allows the MATCH() function to search based on the string plus any number of characters that may follow it.

Paste this into cell B1.

=IF(ISNA(MATCH($G$1,A1,FALSE)),"",LEFT($G$1,SEARCH("*",$G$1,1)))&IF(ISNA(MATCH($G$2,A1,FALSE)),"",LEFT($G$2,SEARCH("*",$G$2,1)))&IF(ISNA(MATCH($G$3,A1,FALSE)),"",LEFT($G$3,SEARCH("*",$G$1,1)))&IF(ISNA(MATCH($G$4,A1,FALSE)),"",LEFT($G$4, SEARCH("*",$G$1,1)))&IF(ISNA(MATCH($G$5,A1,FALSE)),"",LEFT($G$5, SEARCH("*", $G$5,1)))

You'll have to repeat the "&IF(ISNA(MATCH($G$5,A1, FALSE)), "", LEFT($G$5,SEARCH("*",$G$5,1)))" portion for each cell in Column G where you have a string you want to search.

Then copy the formula in B1 all the way down by double-clicking on the cross-hair in the bottom right hand corner of the cell.

This will not place anything in the cell if there is not match. It's also not case sensitive, but I'll be the first to admit that if your data is in all caps as it appears, then go for the answer above. It's simpler.

Collectives™ on Stack Overflow

excel search for a string in a table and return string if true

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related