Formula that finds a match for cells in Column J in Book1.xls anywhere in Column N in Book2.xls

Book1 Column J has more values than Column N in Book2.xls, and they are in different order. Furthermore the entries in Book1 Column J often have spaces that the entries in Book2 Column N don't have (see example below). Therefore we need something that ignores spaces and just searchesto match all the characters excluding spaces. (I think this is possible, I don't know).

Also, if this is of any relevance, how do I get rid of all the spaces in a cell, since there might be 20 or 30 spaces in a given entry?


I hope this makes sense.

Thanks,
John
Book1 Column J:
28 DEF LIGHTS, 32DEF LIGHTS CANNOT GO OFF    (has trailing spaces as well)

Book2 Column N:
28DEF LIGHTS, 32 DEF LIGHTS CANNOT GO OFF

SUBSTITUTE FORMULA:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(N2," ","",1)," ","",1)," ","",1)

Open in new window

LVL 1
gabrielPennybackReliability Business Tools Analyst IIAsked:
Who is Participating?
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
oops, use SUBSTITUTE

=MATCH(SUBSTITUTE(A1, " ", ""), DATA, 0)
0
 
HainKurtSr. System AnalystCommented:
use

=MATCH(TRIM(A1),DATA,0)

where DATA is your lookup range
0
 
HainKurtSr. System AnalystCommented:
check this sample...
LookupRange.xls
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
barry houdiniCommented:
TRIM will remove all leading and trailing spaces and will convert multiple internal spaces to single ones - so using TRIM only won't give a match between J2 and J5 in your example above because 28DEF won't match - to compare regardless of spaces use SUBSTITUTE, e.g. to remove all spaces from A1

=SUBSTITUTE(A1," ","")

so to match without spaces being considered, in lookup value and match range

=MATCH(SUBSTITUTE(N2," ",""),SUBSTITUTE(J$2:J$10," ",""),0)

needs to be confirmed with CTRL+SHIFT+ENTER

regards, barry
0
 
gabrielPennybackReliability Business Tools Analyst IIAuthor Commented:
Thanks for the posts. "=MATCH(TRIM(A1),DATA,0)
" would work great except I need to get rid of all the spaces, not just the leading and trailing ones. I can create a helper column which gets rid of the zeros. ideally with formulas but if necessary with a function or macro

See attached workbook.

Thanks,
John
LookupRange-2-.xls
0
 
HainKurtSr. System AnalystCommented:
try

=MATCH(replace(A1, " ", ""), DATA, 0)
0
 
barry houdiniConnect With a Mentor Commented:
That will work with those examples because Data range doesn't contain any spaces. If the target range has spces then you need to use substitute on that range too......as I suggested above, so using that same formula but revising for the ranges here try this formula

=MATCH(SUBSTITUTE(F4," ",""),SUBSTITUTE(DATA," ",""),0)

confirmed with CTRL+SHIFT+ENTER

regards, barry
0
 
gabrielPennybackReliability Business Tools Analyst IIAuthor Commented:
Thanks, gentlemen. Hain's works perfectly for my current needs but I appreciate Barry's refinement which might come in handy later.

Thanks,
John
0
All Courses

From novice to tech pro — start learning today.