Search all of Column A, for the value listed in that Column C, Return Value in Column B
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
Best Answer
-
You would use an INDEX/MATCH for this:
=INDEX([Client ID]:[Client ID], MATCH([Facility Parent Name]@row, [Facility Name]:[Facility Name], 0))
Answers
-
You would use an INDEX/MATCH for this:
=INDEX([Client ID]:[Client ID], MATCH([Facility Parent Name]@row, [Facility Name]:[Facility Name], 0))
-
Ah perfect, thank you so much!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!