Solved

vlookup with MID formula and some spaces

Posted on 2016-07-18
9
132 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 33

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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 33

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

828 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