Vlookup but names are written in an order different from the reference data

[taro123]

Hi all,

I am trying to retrieve data from column B (See table A) but the name list (See table B) that I got is different from the reference list.

The data below are all hypothetical:

Table A

Jenny Elizabeth Mary	$10,000
James Tan Jun Kiat	$5,000
Mohammed Iskandar Bin Syahrul Ahmad	$15,000
Eileen Lim	$2,000
Jacky How Ling Ling	$1,000

Table B

Elizabeth Mary Jenny

Tan Jun Kiat James

Iskandar Bin Syahrul Ahmad

Lim, Eileen

How Ling Ling, Jacky

I want to return the second column in Table A ($dollars) to the names in table B, but Vlookup does not work due to the names being written in different order. Would appreciate any help, thanks!

 

[p45cal]

You could try a fuzzy match in Power Query. In the attached it has worked well with the default similarity values. Your two tables on the left, the green table is the results table which can be updated if either (or both) the tables on the left are changed, by right clicking on the green table and choosing Refresh.

 

[p45cal]

@taro123 doesn't bother acknowledging people who try to help, shame because helpers will only return the compliment.

 

[pecoflyer]

@taro123 doesn't bother acknowledging people who try to help, shame because helpers will only return the compliment.

It is, indeed, frustrating...