Help with a formula search for text in a cell from a lookup table

[PJJTXLS]

Hi there

Newby here so not sure how much/little info to post, moderate excel skills, slowly going mad over a project I really please need help with! I have searched this forum and Google with no luck.

Heres my problem - I have a XLS with 20,000+ rows and I need to check if words match a table of 200+ options. If there is an exact match with the the table I need to return the match data.

Following is a real example

Column 1 is the Data Set (about 20k+ records)
Column 2 should be the mysterious formula that eludes me
Column 3 is the lookup range

What I need is for a formula to see if a word or word string matches in a lookup table and if a match (exact but case sensitive not necessary) return that lookup value. I have used red to hopefully make it clearer

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Data[/TD]
[TD]Formula Result (Exact match from lookup table Table)[/TD]
[TD]Lookup table[/TD]
[/TR]
[TR]
[TD]Adobe Reader Installed[/TD]
[TD]Adobe Reader[/TD]
[TD]Adobe Illustrator[/TD]
[/TR]
[TR]
[TD]Updated Adobe Flash Player[/TD]
[TD]Flash[/TD]
[TD]Microsoft Office[/TD]
[/TR]
[TR]
[TD]Microsoft Internet Explorer 10 or 8[/TD]
[TD]FALSE or #NA[/TD]
[TD]Internet Explorer 8[/TD]
[/TR]
[TR]
[TD]New version of Microsoft Office Excel[/TD]
[TD]Microsoft Office[/TD]
[TD]Update Oracle[/TD]
[/TR]
[TR]
[TD]Update Oracle Java SE Multiple Versions[/TD]
[TD]Update Oracle[/TD]
[TD]Wireshark v10[/TD]
[/TR]
[TR]
[TD]Oracle Java SE Single Instance Update[/TD]
[TD]FALSE or #NA[/TD]
[TD]Flash[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Adobe Reader[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Microsoft Malware Protection Et[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Microsoft Word[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]etc[/TD]
[/TR]
</tbody>[/TABLE]




I have tried variants around =ISNUMBER(MATCH(value,range,0)) but I can't find a way to return the successful match.

Ideally I would like to avoid going down an array path if possible but if it is the only solution that's ok.

Any help would be much appreciated, my sanity is at stake!

Cheers Paul

 

[pgc01]

Hi Paul
Welcome to the board

For the example you posted, try in B2:

=IFERROR(LOOKUP(2,1/SEARCH($C$2:$C$10,A2),$C$2:$C$10),"N/A")
Copy down

 

[PJJTXLS]

PGC01

If you can excuse an Aussie term - you bl00dy beauty mate! Works a dream!Thank you so much!

I have copied it into my data set and extended the ranges. The one thing I found that had me stumped for a bit is is that if I extend the lookup range e.g. to $C$2:$C$10000 and from C$9000 on are blank changes all formula results to 0. But that's an easy fix, as C: will continually be added to I'll just ad "XXXXXXXX" as a placeholler to any spare cells in the range and it works perfectly.

I'll throw a prawn on the barbie and raise a coldie to you mate.

Cheers Paul

 

[jtakw]

Hi,

If you add a test for Non-blanks in the formula, you won't need "XXXXXXXX" as a placeholder, and since #N/A is acceptable to you when no matches are found, you can also do away with the IFERROR...

Leave blank cells in Column C Blank, add data as you progress, B2 formula copied down, noticed I've used the C range C2:C20

Book1
	A	B	C
1	Data	Formula Result (Exact match from lookup table Table)	Lookup table
2	Adobe Reader Installed	Adobe Reader	Adobe Illustrator
3	Updated Adobe Flash Player	Flash	Microsoft Office
4	Microsoft Internet Explorer 10 or 8	#N/A	Internet Explorer 8
5	New version of Microsoft Office Excel	Microsoft Office	Update Oracle
6	Update Oracle Java SE Multiple Versions	Update Oracle	Wireshark v10
7	Oracle Java SE Single Instance Update	#N/A	Flash
8			Adobe Reader
9			Microsoft Malware Protection Et
10			Microsoft Word
11
12
Sheet663

Cell Formulas
Range	Formula
B2	=LOOKUP(2,1/(SEARCH(C$2:C$20,A2)*(C$2:C$20<>"")),C$2:C$20)

 

[PJJTXLS]

jatkw

DItto to you bl99dy awesome. Thank you so much for your help

Tested and works perfectly.

Raise a coldie (beer) to you to.

Cheers Paul

 

[jtakw]

You're welcome, welcome to the forum, and thanks for the feedback.