Solved

vlookup with MID formula and some spaces

Posted on 2016-07-18
9
210 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
[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
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

630 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