Search all of Column A, for the value listed in that Column C, Return Value in Column B

CathyP712

Hi All,

I'm having a bit of an issue trying to figure out this formula in smartsheet. I have a sheet with the following columns:

• Facility Name (Column 1: This is a listing of all facilities we are contracted with)
• Client ID (Column 2: This is a unique identifying number for that facility)
• Facility Parent Name (Column 3: If the facility is a "child" of another facility, the "parent" facility name goes here)

The "Facility Name" column has to be the 1st column since it's used for various V-Lookups with other sheets.

All the names listed in the "Facility Parent Name" are also listed in their own row in the "Facility Name" column.

I want Smartsheet to look at the value in the "Facility Parent Name" column, and search the "Facility Name" column for that same value. Once it finds that value, then it should provide the value listed in the "Client ID" column next it.

Here is an example of what I am trying to do, with the desired results in the "Parent Client ID" field.

Can anyone help me with the formula for this?

Thank you!

Cathy

Paul Newcome

You would use an INDEX/MATCH for this:

=INDEX([Client ID]:[Client ID], MATCH([Facility Parent Name]@row, [Facility Name]:[Facility Name], 0))

Paul Newcome

You would use an INDEX/MATCH for this:

=INDEX([Client ID]:[Client ID], MATCH([Facility Parent Name]@row, [Facility Name]:[Facility Name], 0))

CathyP712

https://community.smartsheet.com/discussion/comment/412778#Comment_412778

Ah perfect, thank you so much!

Paul Newcome

Happy to help. 👍️