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

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]A/C[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]ASH001[/TD]
[TD]ASHTON ACCOUNTANTS[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]ABS001[/TD]
[TD]AROMAS[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]ARO001[/TD]
[TD]ARON LTD[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]ARR001[/TD]
[TD]ARRAN AROMATICS[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]BET001[/TD]
[TD]BETTER NATURE LIMITED[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]AFF001[/TD]
[TD]FUTURE RESTORE[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]HAR001[/TD]
[TD]HARRISON PACKAGING LTD[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]HEA001[/TD]
[TD]HEALTH BITE[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]HEA002[/TD]
[TD]HEALTH RESTORE[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]HEA003[/TD]
[TD]HEALTH STORE[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]HEA004[/TD]
[TD]HEALTHY LIVING[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]NEW001[/TD]
[TD]NEW SOLUTIONS[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]WOO001[/TD]
[TD]WOODLY HEALTHCARE - FIFE[/TD]
[/TR]
</tbody>[/TABLE]


The destination sheet...

<strike></strike>[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]A/C
[/TD]
[TD]Name[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]ABS001[/TD]
[TD]AROMAS LIMITED[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]BET001[/TD]
[TD]BETTER NATURE LIMITED[/TD]
[TD]

500​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]BET001[/TD]
[TD]BETTER NATURE LIMITED[/TD]
[TD]

400​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]BET001[/TD]
[TD]BETTER NATURE LIMITED[/TD]
[TD]

50​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]BET001[/TD]
[TD]BETTER NATURE LIMITED[/TD]
[TD]

56​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]HEA001[/TD]
[TD]HEALTH BITE[/TD]
[TD]

789​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]HEA004[/TD]
[TD]HEALTHY LIVING LTD[/TD]
[TD]

356​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]HEA004[/TD]
[TD]HEALTHY LIVING LTD[/TD]
[TD]

78​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]HEA004[/TD]
[TD]HEALTHY LIVING LTD[/TD]
[TD]

59​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]HEA004[/TD]
[TD]HEALTHY LIVING LTD[/TD]
[TD]

987​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]HEA004[/TD]
[TD]HEALTHY LIVING LTD[/TD]
[TD]

567​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]HEA004[/TD]
[TD]HEALTHY LIVING LTD[/TD]
[TD]

987​

[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]HEA004[/TD]
[TD]HEALTHY LIVING LTD[/TD]
[TD]

368​

[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]HEA004[/TD]
[TD]HEALTHY LIVING LTD[/TD]
[TD]

688​

[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]HEA004[/TD]
[TD]HEALTHY LIVING LTD[/TD]
[TD]

9875​

[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]HEA004[/TD]
[TD]HEALTHY LIVING LTD[/TD]
[TD]

3536​

[/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]HEA004[/TD]
[TD]HEALTHY LIVING LTD[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]

#N/A​

[/TD]
[TD]HONEYBEE COMPANY LIMITED[/TD]
[TD]

252​

[/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]WOO001[/TD]
[TD]WOODLY HEALTHCARE[/TD]
[TD]

23​

[/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]WOO001[/TD]
[TD]WOODLY HEALTHCARE[/TD]
[TD]

45​

[/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]WOO001[/TD]
[TD]WOODLY HEALTHCARE[/TD]
[TD]

56​

[/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]WOO001[/TD]
[TD]WOODLY HEALTHCARE[/TD]
[TD]

76​

[/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]WOO001[/TD]
[TD]WOODLY HEALTHCARE[/TD]
[TD]

678​

[/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]WOO001[/TD]
[TD]WOODLY HEALTHCARE[/TD]
[TD]

543​

[/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD]WOO001[/TD]
[TD]WOODLY HEALTHCARE[/TD]
[TD]

456​

[/TD]
[/TR]
[TR]
[TD]

27​

[/TD]
[TD]WOO001[/TD]
[TD]WOODLY HEALTHCARE[/TD]
[TD]

234​

[/TD]
[/TR]
[TR]
[TD]

28​

[/TD]
[TD]WOO001[/TD]
[TD]WOODLY HEALTHCARE[/TD]
[TD]

234​

[/TD]
[/TR]
[TR]
[TD]

29​

[/TD]
[TD]WOO001[/TD]
[TD]WOODLY HEALTHCARE[/TD]
[TD]

234​

[/TD]
[/TR]
</tbody>[/TABLE]


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.