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