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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!