Help & Advice On A VLookup/Match Maybe?

[Dazzawm]

I don't really know how to explain this!! I have 2 sheets with a massive amount of data. Sheet 1 will have numbers with a 2 letter prefix then 4 or 5 numbers then letters again, and so will sheet 2. I need to match up ignoring the last letters.

Excel 2010
	A
1	PartNo
2	WA11002R
3	WA11005N
4	WA11008N
5	WA11011N
6	WA11012N
7	WA11013N
8	WA11014N
9	WA11016N
10	WA11020N
11	WA11024N
12	WA11025N
13	WA11031R
14	WA11032N
15	WA11041N
16	WA11042N-OS
Sheet1

Excel 2010
	A
1	PartNo
2	WA11002N
3	WA11005R
4	WA11008R
5	WA11011R
6	WA11012R
7	WA11013N
8	WA11014N
9	WA11016R
10	WA11020R
11	WA11024N
12	WA11025R
13	WA11031R
14	WA11032R
15	WA11041N
16	WA11042N
Sheet2

As you can see some end with R and some end with N but I need the formula or code to match up the prefix and numbers so the result would look like below. Obviously using just VLookup it would return a lot of N/A because they need to be an exact match. I hope someone can make some sense of this!!

Intended Result

Excel 2010
	A	B
1	PartNo	PartNo
2	WA11002R	WA11002N
3	WA11005N	WA11005R
4	WA11008N	WA11008R
5	WA11011N	WA11011R
6	WA11012N	WA11012R
7	WA11013N	WA11013N
8	WA11014N	WA11014N
9	WA11016N	WA11016R
10	WA11020N	WA11020R
11	WA11024N	WA11024N
12	WA11025N	WA11025R
13	WA11031R	WA11031R
14	WA11032N	WA11032R
15	WA11041N	WA11041N
16	WA11042N-OS	WA11042N
Sheet1

 

[jkpieterse]

You can use VLOOKUP with a wildcard:

=VLOOKUP(LEFT(A2,LEN(A2)-1)&"*",Sheet2!$A$2:$A$100,1,FALSE)

 

[Dazzawm]

Thanks but that's not working for me, just returning #N/A all the way down.

 

[jkpieterse]

Can you post a link to a sample Excel file please? Without any sensitive data of course...

 

[LuKate]

Your sample data shows just a single letter as the suffix, but your explanation implies there could be more than 1 letter as a suffix.

Try this in sheet2 Cell B2 copied down. NOTE: it is an array formula so must be confirmed with ctrl+shift+enter, not just enter

=VLOOKUP(LEFT(A2,MATCH(TRUE,ISERROR(VALUE(MID(A2,ROW(INDIRECT("3:"&LEN(A2))),1))),0)+1)&"*",Sheet1!$A$2:$A$2000,1,FALSE)

 

[Yongle]

If always
2-letter suffix
followed by either 4 OR 5 Numbers

then try this

=IFERROR(VLOOKUP(LEFT(A2,7)&"*",Sheet2!A:A,1,0),VLOOKUP(LEFT(A2,6)&"*",Sheet2!A:A,1,0))

Excel 2016 (Windows) 32 bit

	A​	B​	C​
1​	Part Number	Result	formula in B2 copied down
2​	WA11002R	WA11002N	=IFERROR(VLOOKUP(LEFT(A2,7)&"*",Sheet2!A:A,1,0),VLOOKUP(LEFT(A2,6)&"*",Sheet2!A:A,1,0))
3​	WA11005N	WA11005R
4​	WA11008N	WA11008R
5​	WA11011N	WA11011R
6​	WA11012N	WA11012R
7​	WA11013N	WA11013N
8​	WA11014N	WA11014N
9​	WA11016N	WA11016R
10​	WA11020N	WA11020R
11​	WA11024N	WA11024N
12​	WA11025N	WA11025R
13​	WA11031R	WA11031R
14​	WA11032N	WA11032R
15​	WA11041N	WA11041N
16​	WA11042N-	WA11042N

Sheet: Sheet1

 

[Dazzawm]

Your sample data shows just a single letter as the suffix, but your explanation implies there could be more than 1 letter as a suffix.

Try this in sheet2 Cell B2 copied down. NOTE: it is an array formula so must be confirmed with ctrl+shift+enter, not just enter

=VLOOKUP(LEFT(A2,MATCH(TRUE,ISERROR(VALUE(MID(A2,ROW(INDIRECT("3:"&LEN(A2))),1))),0)+1)&"*",Sheet1!$A$2:$A$2000,1,FALSE)

Seems to work great, thanks for your help. Also jkpieterse.

 

[LuKate]

You're welcome.