search and return cell values

[ronaldcam]

I have the below example table. I need to match values in column a width value in column d and return the value in column c. The problem is that the value in column a are the first name, middle name and last name and are not always in the same order. For example, they can be as per below:

First name Middle Name Last name
First name Last name Middle name
Last name First name Middle name

but one of the combinations from column a will match with column d and then return the value i column c. For example if I have the below table it will return 1 since the value in column d is one possible value of column a.

a	b	c	d
Ron Laf Cam		1	Laf Ron Cam

thanks for your help

 

[bosco_yip]

Try..........

In C2, formula copied down :

=0+(COUNT(SEARCH(TRIM(MID(SUBSTITUTE(D2," ",REPT(" ",49)),{1,49,99},50)),A$2))=3)

 

[ronaldcam]

Try..........

In C2, formula copied down :

=0+(COUNT(SEARCH(TRIM(MID(SUBSTITUTE(D2," ",REPT(" ",49)),{1,49,99},50)),A$2))=3)

View attachment 69921

forgot to explain that the match can be anywhere in the list. They dont match exactly row by row

 

[ronaldcam]

forgot to explain that the match can be anywhere in the list. They dont match exactly row by row