Solved

vlookup with MID formula and some spaces

Posted on 2016-07-18
9
163 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
Independent Software Vendors: 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!

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 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