John Carney
asked on
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
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)
check this sample...
LookupRange.xls
LookupRange.xls
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
=SUBSTITUTE(A1," ","")
so to match without spaces being considered, in lookup value and match range
=MATCH(SUBSTITUTE(N2," ",""),SUBSTITUTE(J$2:J$10,
needs to be confirmed with CTRL+SHIFT+ENTER
regards, barry
ASKER
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
" 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
try
=MATCH(replace(A1, " ", ""), DATA, 0)
=MATCH(replace(A1, " ", ""), DATA, 0)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, gentlemen. Hain's works perfectly for my current needs but I appreciate Barry's refinement which might come in handy later.
Thanks,
John
Thanks,
John
=MATCH(TRIM(A1),DATA,0)
where DATA is your lookup range