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

PJJTXLS

New Member
Joined
May 29, 2019
Messages
3
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

DataFormula Result (Exact match from lookup table Table)Lookup table
Adobe Reader InstalledAdobe ReaderAdobe Illustrator
Updated Adobe Flash PlayerFlashMicrosoft Office
Microsoft Internet Explorer 10 or 8FALSE or #NAInternet Explorer 8
New version of Microsoft Office ExcelMicrosoft OfficeUpdate Oracle
Update Oracle Java SE Multiple VersionsUpdate OracleWireshark v10
Oracle Java SE Single Instance UpdateFALSE or #NAFlash
Adobe Reader
Microsoft Malware Protection Et
Microsoft Word
etc

<tbody>
</tbody>




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!:eek:

Cheers Paul
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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
ABC
1DataFormula Result (Exact match from lookup table Table)Lookup table
2Adobe Reader InstalledAdobe ReaderAdobe Illustrator
3Updated Adobe Flash PlayerFlashMicrosoft Office
4Microsoft Internet Explorer 10 or 8#N/AInternet Explorer 8
5New version of Microsoft Office ExcelMicrosoft OfficeUpdate Oracle
6Update Oracle Java SE Multiple VersionsUpdate OracleWireshark v10
7Oracle Java SE Single Instance Update#N/AFlash
8Adobe Reader
9Microsoft Malware Protection Et
10Microsoft Word
11
12
Sheet663
Cell Formulas
RangeFormula
B2=LOOKUP(2,1/(SEARCH(C$2:C$20,A2)*(C$2:C$20<>"")),C$2:C$20)
 
Upvote 0
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
 
Upvote 0
You're welcome, welcome to the forum, and thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top