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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Part Number[/td][td]Result[/td][td] formula in B2 copied down[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]WA11002R[/td][td]WA11002N[/td][td] =IFERROR(VLOOKUP(LEFT(A2,7)&"*",Sheet2!A:A,1,0),VLOOKUP(LEFT(A2,6)&"*",Sheet2!A:A,1,0))[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]WA11005N[/td][td]WA11005R[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]WA11008N[/td][td]WA11008R[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]WA11011N[/td][td]WA11011R[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]WA11012N[/td][td]WA11012R[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]WA11013N[/td][td]WA11013N[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]WA11014N[/td][td]WA11014N[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]WA11016N[/td][td]WA11016R[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]WA11020N[/td][td]WA11020R[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]WA11024N[/td][td]WA11024N[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]WA11025N[/td][td]WA11025R[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]WA11031R[/td][td]WA11031R[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]WA11032N[/td][td]WA11032R[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]WA11041N[/td][td]WA11041N[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]WA11042N-[/td][td]WA11042N[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[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.