Solved

vlookup with MID formula and some spaces

Posted on 2016-07-18
9
110 Views
Last Modified: 2016-07-29
Hello experts,

could someone provide me with a formula that will do a vlookup with a MID formula but where there are sometimes spaces.

i.e.

MD9 Look this up ME1 with no spaces


so my VLOOKUP would be something like:

=VLOOKUP(MID(C2,18,5),Sheet2!,J:K,2,0)

to return the number 123456

but where the cell has a space in this one: 'ME1 2'

MD9 Look this up ME1 2 with a space

It returns an error.

Thanks in advance
0
Comment
Question by:Jagwarman
  • 4
  • 3
  • 2
9 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41717209
To clarify certain things please upload a sample excel file showing the working and not working cases
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41717386
How about this?

=VLOOKUP(SUBSTITUTE(MID(C2,18,5)," ",""),Sheet2!,J:K,2,0)

The substitute function replaces the specified text with a new text, in this case will replace all spaces with nothing.

Syntax:  =SUBSTITUTE(Text, Old Value, New Value, Instance)
0
 

Author Comment

by:Jagwarman
ID: 41717874
I have attached a file which hopefully makes it more clear.

Thanks
lookup.xls
0
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
LVL 32

Expert Comment

by:Rob Henson
ID: 41717917
Can you give a more realistic example? I assume your real data doesn't say:

"MD9 Look this up ME1 with no spaces"

You obviously want to find a value to lookup within another string. What is the configuration of the real string? We can then give a way of finding the required string within it.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 41718140
For the given scenario this works in excel 2007 or later. For earlier versions of excel or for a more complex set of data you might have to resort to helper columns due to limitation of nested levels and inavailability of the iferror function.

=VLOOKUP(MID(C2,18,FIND(" ",MID(C2,18,100))-1)&IFERROR(" "&VALUE(MID(C2,18+LEN(MID(C2,18,FIND(" ",MID(C2,18,100)))),1)),""),Sheet2!J2:K8,2,0)
0
 

Author Comment

by:Jagwarman
ID: 41718192
I am looking up ME1 or ME2 or ME1 1 or ME2 2 or ME3 or ME3 2 etc
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41718195
The given formula should work. Tell me if it does not. Also upload a sample file in this case.
0
 

Author Comment

by:Jagwarman
ID: 41725143
Sorry Saqib Husain, Syed been so busy at work and believe it or not I don't have access to internet from my PC so I have do test at home then copy code take to work type code in and test. It works great on my home PC will test it at work on Monday. Have a great weekend.
0
 

Author Comment

by:Jagwarman
ID: 41728827
Hi Saqib Husain, Syed tested and works perfectly. Many thanks and sorry for the delay.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

803 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