how to combine vlookup and IF(ISNUMBER(SEARCH("XXX",A1)),A1,"") ?

[spyrule]

Hello,

I need help on a formula that can search for text in ColumnA against a list of words in ColumnX, if ColumnA contains any matches against a Named Range (which consists of ColumnX and ColumnY) return the matched value from ColumnY.

I know a vlookup will return the matched value. however, it wants to compare the ENTIRE source and doesn't seem to match multiple words (even if I set the comparison to true) .

For example:
ColumnA contains text like :

Rear Brake Pad Set 11+ Scion TC
Front Brake Pads (09+ Honda Fit)
Front Ceramic Brake Pad Set (2012 Elantra)
Euro Ceramic Rear Pads - BMW
Racing Glove Medium (Red)

I then need to search a table with
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]ColumnX[/TD]
[TD]ColumnY[/TD]
[/TR]
[TR]
[TD]Brake Pads[/TD]
[TD]Brakes[/TD]
[/TR]
[TR]
[TD]Brake Pad Set[/TD]
[TD]Brakes[/TD]
[/TR]
[TR]
[TD]Ceramic Rear Pads[/TD]
[TD]Brakes[/TD]
[/TR]
[TR]
[TD]Clock Spring[/TD]
[TD]Steering Wheel Accessories[/TD]
[/TR]
[TR]
[TD]Gloves[/TD]
[TD]Gloves[/TD]
[/TR]
</tbody>[/TABLE]











As you can see, I need to search a rather large (will likely be 2000+ rows) of comparison text fields, and return the matching ColumnY value.

Is there a way to combine a vlookup and an ISNUMBER(FIND(substring,text)) search ?

Thanks in advance,

Spyrule

 

[Oaktree]

Try:

=INDEX($Y$1:$Y$5,MATCH(TRUE,ISNUMBER(SEARCH($X$1:$X$5,A1)),0)), which is an array formula and must be confirmed with CTRL+SHIFT+ENTER instead of just ENTER (doing so correctly will have Excel put { }s around your formula in the formula bar).

Then, copy the cell with that formula and paste to the rest of the cells in the applicable range.

Note also that you'll want to change "Gloves" to "Glove" in cell X5 if you want "Racing Glove Medium (Red)" to map to "Gloves" in column Y.

 

[spyrule]

So, this works, but man is it slow to process. I've got ~5000 rows to process, and I modified the formula to this:

=IF(ISNA(INDEX(ComparisonResults,MATCH(TRUE,ISNUMBER(SEARCH(ComparisonList,E175)),0))),"Offline",IF(INDEX(ComparisonResults,MATCH(TRUE,ISNUMBER(SEARCH(ComparisonList,E175)),0))=0,"Offline",INDEX(ComparisonResults,MATCH(TRUE,ISNUMBER(SEARCH(ComparisonList,E175)),0))))

So far at 10 minutes, I'm at 25% complete.

 

[M Asim]

ISNUMBER

-1 I want to search in row (Example: Item No. S-43, S-42) and search result in next column Result (S-43) (It is big list who i can do it) (Searh, istnumber, find etc.)

 

[M Asim]

I want to search in row (Example: Item No. S-43, S-42) and search result in next column Result (S-43) (It is big list who i can do it) (Searh, istnumber, find etc.)

 

[M Asim]

IF(ISNUMBER(SEARCH("XXX",A1)),A1,"") ?
"XXX" Value is Big List

 

[Fluff]

@M Asim
Please start a thread of your own, rather than jumping on an old thread.
But give your thread a meaningful title (not your name) & explain clearly what you are trying to do.
Thanks

 

[M Asim]

=INDEX({"S-842","S-843","S-844","S-722","H5","H6","H7","H8","H9","H10","No"},MATCH(1,COUNTIF(B7,"*"&{"S-842","S-843","S-844","S-722","H5","H6","H7","H8","H9","H10",""}&"*"),0))

 

[M Asim]

My File

 

[Fluff]

@M Asim
Please start a new thread for your question as requested.