We have a sheet 2 that gets imported for sake of argument say we have column A contains a list of first names, column B is their associated age i.e A1=John B1:32

on our first sheet 1 we have column a with a dropdown with Complete Names (first/last) and I want to populate column B with their associated age from sheet2 i.e. A1=John Doe B1=32

This is our data simplified for need...

So basically Sheet1 column B needs to search Sheet2:A for the first name and return the age based on the dropdown value of Sheet1:A

I'm sure its a matter of combining the right vlookup, regexmatch or something else...many thanks up front

CodePudding user response：

Sample Data (Sheet2):

Formula:

=vlookup(regexreplace(A2, " .*", ""), Sheet2!A:B, 2, false)

• regexreplace above will return the first name (name before the first space). You can also use regexextract alternatively.
• vlookup will then find that first name that has been returned in Sheet2!A:A and return its respective value in Sheet2!B:B

Output (Sheet1):