VLOOKUP the middle characters, and ignore the first 3 characters and the last 2

Hey everyone,

So my title is my issue, in a nutshell.

I am looking up contact info that is attached to an ID number. This is so I can place the info on one worksheet, to another......but that's the simple part

The ID number looks like this "AB-123456A1"

I need the data on the contact list to remain like it looks above.....so I can't get rid of the first 3 characters, or last two characters........but I was wondering if it would be possible to use the VLOOKUP function......to lookup the characters in between?

Please feel free to share any tips, or sample formulas based on any examples you can think of. Thanks guys! I'm really stumped!

Use MID to extract the search value.

Regards, TMS

Hi bradallen and welcome to the forum.

How about creating a helper column with a formula, something like this.
=MID(A1,4,LEN(A1)-5)
Use this helper column for the lookup.

Does that help?

Just use the formula provided as the search item.

=VLOOKUP(MID(A1,4,LEN(A1)-5), search range, column, FALSE)

Regards, TMS

The "-" is searching for a hyphen (minus sign, whatever) in the cell A1.

I suspect that it won't give you what you want as you have other data after the numeric part.

Regards, TMS

Assuming that you always lose three characters at the start and two characters at the end, MarvinP's formula will work.

This amendment caters for blank cells:

=IFERROR(MID(A2,4,LEN(A2)-5),"")

This is a variation on rundie_d's formula:

=IFERROR(MID(A4,FIND("-",A4,1)+1,LEN(A4)-FIND("-",A4,1)-2),"")

This still assumes that you lose two characters at the end but you can have whatever you want before the hyphen.


Regards, TMS

Have a look at the previous posts ... use the formula provided as the search item in the VLOOKUP.

=IF(RIGHT(A2,2)="A1",IFERROR(MID(A2,4,LEN(A2)-3),""),IFERROR(MID(A2,4,LEN(A2)-5),""))


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Feeling of deja vu ... it's the formula that you use as the search item (without the equals sign).


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

=VLOOKUP(IF(RIGHT(A2,2)="A1",IFERROR(MID(A2,4,LEN(A2)-5),""),IFERROR(MID(A2,4,LEN(A2)-3),"")), search_range, rcolumn, FALSE)


where search_range is a named range for the lookup area and rColumn is a named range containing the column to return.

=VLOOKUP(IF(RIGHT(A2,2)="A1",IFERROR(MID(A2,4,LEN(A2)-5),""),IFERROR(MID(A2,4,LEN(A2)-3),"")), $F:$G, 2, FALSE)


Regards, TMS

I am confused. You said:

I need the data on the contact list to remain like it looks above.....so I can't get rid of the first 3 characters, or last two characters........but I was wondering if it would be possible to use the VLOOKUP function......to lookup the characters in between?

So, you got a formula to do that. Then you wanted a VLOOKUP ... and you got a formula to do that.

Then you said some codes don't have A1 so don't lose the last two characters ... and you were given a formula to do that.

After that, you needed the latest formula to be inserted into the VLOOKUP ... and you got it.

This is, as you understand, dragging on and becoming frustrating.

Please post a sample workbook with all the variations that you expect to get, what you want them matched against, and what you expect to be returned. This doesn't require live data, just examples of what you are actually seeing, what, if anything they should match and the value that you want returning.

If you make the sample workbook representative of what you are working with, you may well get an answer that addresses all the issues ... in one pass.

Regards, TMS

So, basically, you're saying: look up the code as it stands - if that's not there, take off the A1 if it's there and try again. And if that's not there, remove the State and try again.

What is the frequency of each of the options? It doesn't make a lot of difference but it makes sense to look for the most frequent option first.

Regards, TMS

With Excel 2007/2010 (from the extension), you could use this:

=IFERROR(VLOOKUP(IFERROR(MID(A2,4,LEN(A2)-3),""),Sheet2!$B:$C, 2, FALSE),"")&IFERROR(VLOOKUP(IFERROR(MID(A2,4,LEN(A2)-5)*1,""),Sheet2!$B:$C, 2, FALSE),"")&IFERROR(VLOOKUP($A2,Sheet2!$B:$C,2,FALSE),"")

Basically, it tries all the options and concatenates the results ... only one will be found (I think) and the others will be blank. This works for the three examples.

If it's Excel 2003 from you profile, it will be the same premise, just longer ... quite a bit longer.


Regards, TMS

Trim what? The name returned? You need to remove leading and trailing spaces? Whatever ...

=TRIM( ... the formula ...)

I don't know. You need to look at your data and see what should and should not match something.

If you have, for example, IL-12345A1 with a description A, 12345A1 with a description B, and 12345 with a description C in the lookup table, with the latest formula, they are all going to match and you will get ABC, but not necessarily in that order. To avoid that, you'd probably need to make it a nested IF which would stop at the first match. I'm not sure what you would prefer ... do you need/want to know if there are multiple matches?

I think you now have the building blocks and need to start experimenting for yourself.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Thanks for the rep.