Vlookup

[Zandra]

I wonder if someone could help, I have two supplier lists. The first has the correct supplier spellings and supplier references. The second is a creditor listing from a different system which has the same suppliers and different account numbers. The supplier names differ slightly eg limited included in the name and the supplier appears more than once if there is more than one invoice outstanding. I am trying to do a vlookup of the correct supplier list and match the names so that I can input the correct supplier reference against the supplier on the creditor listing. It seems to be matching some but where I have suppliers with similar names or where the supplier appears more than once on the creditor listing the wrong reference is being input.

Does anyone know of how I can go about resolving this?

Many Thanks

 

[Aladin Akyurek]

Care to post an illustrative sample along with the desired results?

 

[Zandra]

Care to post an illustrative sample along with the desired results?

Here is the supplier list:

A/C Name
ASH001 ASHTON ACCOUNTANTS
ABS001 AROMAS
ARO001 ARON LTD
ARR001 ARRAN AROMATICS
BET001 BETTER NATURE LIMITED
AFF001 FUTURE RESTORE
HAR001 HARRISON PACKAGING LTD
HEA001 HEALTH BITE
HEA002 HEALTH RESTORE
HEA003 HEALTH STORE
HEA004 HEALTHY LIVING
NEW001 NEW SOLUTIONS
WOO001 WOODLY HEALTHCARE - FIFE

This is the result of using the vlookup. Column 1 is what it should be and column 4 is what the vlookup gives me.

ACTUAL Name Amount VLOOKUP
ABS001 AROMAS LIMITED 100 ARO001
BET001 BETTER NATURE LIMITED 500 AFF001
AFF001 BETTER NATURE LIMITED 400 AFF001
HAR001 BETTER NATURE LIMITED 50 AFF001
HEA001 BETTER NATURE LIMITED 56 AFF001
HEA001 HEALTH BITE 789 HEA004
HEA004 HEALTHY LIVING LTD 356 HEA004
HEA004 HEALTHY LIVING LTD 78 HEA004
HEA004 HEALTHY LIVING LTD 59 HEA004
HEA004 HEALTHY LIVING LTD 987 HEA004
HEA004 HEALTHY LIVING LTD 567 HEA004
HEA004 HEALTHY LIVING LTD 987 HEA004
HEA004 HEALTHY LIVING LTD 368 HEA004
HEA004 HEALTHY LIVING LTD 688 HEA004
HEA004 HEALTHY LIVING LTD 9875 HEA004
HEA004 HEALTHY LIVING LTD 3536 HEA004
HEA004 HEALTHY LIVING LTD 6 HEA004
HON001 HONEYBEE COMPANY LIMITED 252 HEA004
WOO001 WOODLY HEALTHCARE 23 WOO001
WOO001 WOODLY HEALTHCARE 45 WOO001
WOO001 WOODLY HEALTHCARE 56 WOO001
WOO001 WOODLY HEALTHCARE 76 WOO001
WOO001 WOODLY HEALTHCARE 678 WOO001
WOO001 WOODLY HEALTHCARE 543 WOO001
WOO001 WOODLY HEALTHCARE 456 WOO001
WOO001 WOODLY HEALTHCARE 234 WOO001
WOO001 WOODLY HEALTHCARE 234 WOO001
WOO001 WOODLY HEALTHCARE 234 WOO001

Many Thanks

 

[Aladin Akyurek]

Could you also post the VLOOKUP formula you used?

 

[Zandra]

Here is the formula I used:

=VLOOKUP($C2,'SUPPLIER LIST'!$A$2:$B$14,1,TRUE)

Many Thanks

 

[Aladin Akyurek]

SUPPLIER LIST

Row\Col	A​	B​
1​	A/C	Name
2​	ASH001	ASHTON ACCOUNTANTS
3​	ABS001	AROMAS
4​	ARO001	ARON LTD
5​	ARR001	ARRAN AROMATICS
6​	BET001	BETTER NATURE LIMITED
7​	AFF001	FUTURE RESTORE
8​	HAR001	HARRISON PACKAGING LTD
9​	HEA001	HEALTH BITE
10​	HEA002	HEALTH RESTORE
11​	HEA003	HEALTH STORE
12​	HEA004	HEALTHY LIVING
13​	NEW001	NEW SOLUTIONS
14​	WOO001	WOODLY HEALTHCARE - FIFE

<tbody>
</tbody>

The destination sheet...

<strike></strike>

Row\Col	B​	C​	D​
1​	A/C	Name	Amount
2​	ABS001	AROMAS LIMITED	100​
3​	BET001	BETTER NATURE LIMITED	500​
4​	BET001	BETTER NATURE LIMITED	400​
5​	BET001	BETTER NATURE LIMITED	50​
6​	BET001	BETTER NATURE LIMITED	56​
7​	HEA001	HEALTH BITE	789​
8​	HEA004	HEALTHY LIVING LTD	356​
9​	HEA004	HEALTHY LIVING LTD	78​
10​	HEA004	HEALTHY LIVING LTD	59​
11​	HEA004	HEALTHY LIVING LTD	987​
12​	HEA004	HEALTHY LIVING LTD	567​
13​	HEA004	HEALTHY LIVING LTD	987​
14​	HEA004	HEALTHY LIVING LTD	368​
15​	HEA004	HEALTHY LIVING LTD	688​
16​	HEA004	HEALTHY LIVING LTD	9875​
17​	HEA004	HEALTHY LIVING LTD	3536​
18​	HEA004	HEALTHY LIVING LTD	6​
19​	#N/A​	HONEYBEE COMPANY LIMITED	252​
20​	WOO001	WOODLY HEALTHCARE	23​
21​	WOO001	WOODLY HEALTHCARE	45​
22​	WOO001	WOODLY HEALTHCARE	56​
23​	WOO001	WOODLY HEALTHCARE	76​
24​	WOO001	WOODLY HEALTHCARE	678​
25​	WOO001	WOODLY HEALTHCARE	543​
26​	WOO001	WOODLY HEALTHCARE	456​
27​	WOO001	WOODLY HEALTHCARE	234​
28​	WOO001	WOODLY HEALTHCARE	234​
29​	WOO001	WOODLY HEALTHCARE	234​

<tbody>
</tbody>

In B2 of the destination sheet enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH('SUPPLIER LIST'!$B$2:$B$14,$C2&"*"),
  'SUPPLIER LIST'!$A$2:$A$14),VLOOKUP($C2&"*",CHOOSE({1,2},'SUPPLIER LIST'!$B$2:$B$14,
  'SUPPLIER LIST'!$A$2:$A$14),2,0))

<strike></strike>

 

[Zandra]

Thank you! This has been a great help.

 

[Aladin Akyurek]

Thank you! This has been a great help.

You are welcome.