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 a different order. Would appreciate any help, thanks!

 

[Jimmypop]

Hi there and welcome to the forum...

Maybe Xlookup will work?

UPDATE: Tried... not working... anyone else with another suggestion?

 

[Kerryx]

	A	B	C	D
1	Jenny Elizabeth Mary	$10,000	Elizabeth Jenny Mary
2	James Tan Jun Kiat	$5,000	James Jun Kiat Tan
3	Mohammed Iskandar Bin Syahrul Ahmad	$15,000	Ahmad Bin Iskandar Mohammed Syahrul
4	Eileen Lim	$2,000	Eileen Lim
5	Jacky How Ling Ling	$1,000	How Jacky Ling Ling
6
7	Elizabeth Mary Jenny		Elizabeth Jenny Mary	$10,000
8	Tan Jun Kiat James		James Jun Kiat Tan	$5,000
9	Iskandar Bin Syahrul Ahmad		Ahmad Bin Iskandar Syahrul	$15,000
10	Lim, Eileen		Eileen Lim,	$2,000
11	How Ling Ling, Jacky		How Jacky Ling Ling,	$1,000
FG

Cell Formulas
Range	Formula
C1:C5,C7:C11	C1	=TEXTJOIN(" ",TRUE,SORT(FILTERXML("<A><B>"&SUBSTITUTE(A1," ","</B><B>")&"</B></A>","//B")))
D7:D11	D7	=XLOOKUP(C7,$C$1:$C$5,$B$1:$B$5,"",-1)

 

[Jimmypop]

@Kerryx

Good one...learning something new each day...

 

[Kerryx]

Only thing is had to account for comma "," and "Mohammed" in vlookup by using (-1 next smallest), don't know if this will cause you any issues based on the data supplied

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Vlookup but names are written in an order different from the reference data
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[taro123]

Thank you Jimmy and Kerry, solved!