Formula to replicate column values when certain value is matched
My source sheet has 3 columns such has Name, Location ID and Grade. When i provide the Location ID in the target sheet, i would require the Name and Grade values to be populated for that respective Location ID.
-For ex: If Location ID 123 is entered in Target sheet only once, then all the Names and Grade which has same location ID, respective names and grade should be displayed.
And also, when the location ID is changed to another value then the name and grade values to be changed and displayed accordingly.
Source Sheet:
Target Sheet: Location ID value is entered only one time; i need all values as below:
And also, if location ID is changed, then values should change accordingly:
Answers
-
You would use an INDEX/MATCH for this.
=INDEX({Range To Pull From}, MATCH([Location ID]@row, {Source Sheet Location ID Column}, 0))
-
@Paul Newcome : I tried using above mentioned formula, only one matching value is displayed . Could you please help.
Source Sheet 1 has following values:
In Source Sheet 2, will enter Location ID as ID 123
Value displayed using the formula: =INDEX({Source Sheet 1 Name}, MATCH({Source Sheet 2 Location ID}, {Source Sheet 1 Location ID}))
I need a result in Target Sheet as below. All 4 names as Location ID- ID 123. Could you please help me with the formula.
-
Hi @Nishu
I hope you're well and safe!
Try something like this.
=INDEX({Source Sheet 1 Name}, MATCH([Location ID]@row, {Source Sheet 1 Location ID},0))
Did that work/help?
I hope that helps!
Be safe, and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå : I tried using the above mention formula, However, just only one matching value is displayed. Remaining value which matches ID is not displayed.
For example: For Location ID 123; there are 4 values which matches this ID. However, only one value is returned i.e. Cloe is displayed but (Mark, Andrea, Andrew) which has same ID is not returned. I need all the values which matches ID needs to be returned. Could you please help @Andrée Starå
-
Have you looked at pulling a report instead?
-
@Paul Newcome : i won't be able to use reports for this. Sheet is used by multiple users and most of them have Just editor access and i assume, they wont be able to edit filter criteria in report to just return multiple values for particular location, by applying filter criteria and the location ID is not constant, it keeps changing.
I need formula or idea for this. Could you please help me on this.
-
What is the maximum number of rows you would need to pull for a single location?
-
@Paul Newcome : I need to apply formula for 1000 rows. And it depends on the value entered for that particular month. Sometime its weekly / monthly basis.
-
I meant... How many names would be coming over for a single Location ID? What is the max?
-
@Paul Newcome : probably around 60-70 names for single location ID.
-
You will need to pre-populate a column with numbers starting at 1 and going on down until you have filled enough rows. I will call this column "Number" for this example.
If you think 60-70 would be the max then I would suggest going on down to 100 just to give yourself some buffer. You will also need somewhere for users to enter the Location ID.
I will use a Sheet Summary field for this example, but you could use a cell within the sheet if you wanted. Just be sure to update the cell reference where appropriate.
Column Formula:
=INDEX(DISTINCT(COLLECT({Source Sheet Name Column}, {Source Sheet Location ID Column}, @cell = [Location ID]#)), Number@row)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!