Complex formula possibly Index & Match

[DoggoS]

Hi there,

I have 2 sheets containing various employee data - one sheet named 'Mob Data' contains the Employee Full Name (in column A) and the other sheet named 'Master data' contains Employee Full Name (in column A) along with Employee's function (in column F of this sheet).
I need a formula to look up the Employee Name from 'Mob Data' sheet and match it to the Employee Full Name from 'Master data' sheet and if it finds it to then retrieve the Function from this sheet and populate it into the Function column (column B) within the 'Mob Data' sheet.
I hope that makes sense...
Grateful for any assistance - thank you in advance

 

[awoohaw]

try:
place this in cell b1:

=xlookup(A1,'Master Data'!$A$1:$A$500,'Master Data'!$F$1:$F$500)

copy down.

 

[AlanY]

try this in B2 and copy down

=VLOOKUP(A2, 'Master data'!A:F, 6, FALSE)

 

[DoggoS]

try:
place this in cell b1:

=xlookup(A1,'Master Data'!$A$1:$A$500,'Master Data'!$F$1:$F$500)

copy down.

Thank you for this - unfortunately it failed to retrieve the actual data value from the function column but all good the below formula was successful

 

[DoggoS]

try this in B2 and copy down

=VLOOKUP(A2, 'Master data'!A:F, 6, FALSE)

Thank you Alan - this done the trick!

 

[AlanY]

Thank you Alan - this done the trick!

you're welcome

 

[awoohaw]

Thank you for this - unfortunately it failed to retrieve the actual data value from the function column but all good the below formula was successful

mm. works for me. But, you found a solution!