Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

vlookup with MID formula and some spaces

Posted on 2016-07-18
9
Medium Priority
?
293 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 34

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 34

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 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

927 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