Need a formula to return a value by looking a table from another sheet.

Swetha

I have Source sheet with a list of names. In Target sheet, I will be having a column with other set of names. Need to compare the names in Target sheet with Source sheet and return the value which has same name. However, if the name list in the Target sheet does not match the name list in the Source sheet then "NA" value needs to be returned.

Example: Source Sheet:

Target Sheet:

Kelly Moore

Hey @Swetha

There are a number of approaches for your question, Here's one:

=IFERROR(INDEX({Source Sheet Source Name}, MATCH([Target Name]@row, {Source Sheet Source Name}, 0)), "NA")

Will this work for you?

Kelly

Swetha

@Kelly Moore : Thank you, it works for the above mentioned scenario.

Is there any possibility that formula can be tweaked to include return value which include the names which appended with different last names.

However, in Source sheet, sometimes there wont be just first names. But, i still need a value to be returned, if we have first name in Source sheet no matter whatever the value is appended to first name.

Can you please take a look?

Target Sheet:

Kelly Moore

Hey @Swetha

Yes, I can do that. Is it possible you send a screenshot of the source sheet that shows how the first and last names are arranged in your sheet? Or, do you have them already joined in a different column rather than Name?

Swetha

@Kelly Moore : Source sheet names will be having just first names as shown in the attached screenshot above. Source sheet wont have last names.

However, in Target sheet; in Target Name column i will be having all the list of entries with

1. just first name
2. some with first & Last name
3. some without matching names in source sheet.

As highlighted in Red in the Target sheet, some with first & last names; where i have matching first name in Source sheet highlighted in Lavender. Also, as it is already in formula if it is not at all matching then NA needs to be returned.

Source Sheet:

Target Sheet:

Kelly Moore

Hey @Swetha

Try this.

=IF(COUNTIFS({Source Sheet Name}, CONTAINS(@cell, [Target Name]@row) = true) > 0, [Target Name]@row, "NA")

Will this work for you?

Kelly