Solved

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

Posted on 2011-02-16
8
334 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:gabrielPennyback
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34910491
use

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

where DATA is your lookup range
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34910497
check this sample...
LookupRange.xls
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34910870
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 1

Author Comment

by:gabrielPennyback
ID: 34911128
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
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34911192
try

=MATCH(replace(A1, " ", ""), DATA, 0)
0
 
LVL 53

Accepted Solution

by:
Huseyin KAHRAMAN earned 350 total points
ID: 34911241
oops, use SUBSTITUTE

=MATCH(SUBSTITUTE(A1, " ", ""), DATA, 0)
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 150 total points
ID: 34911323
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
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 34923034
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question