Formula to replicate column values when certain value is matched

Nishu

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:

Paul Newcome

You would use an INDEX/MATCH for this.

=INDEX({Range To Pull From}, MATCH([Location ID]@row, {Source Sheet Location ID Column}, 0))

Nishu

@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.

Andrée Starå

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!

Nishu

@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å

Paul Newcome

Have you looked at pulling a report instead?

Nishu

@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.

Paul Newcome

What is the maximum number of rows you would need to pull for a single location?

Nishu

@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.

Paul Newcome

I meant... How many names would be coming over for a single Location ID? What is the max?

Nishu

@Paul Newcome : probably around 60-70 names for single location ID.

Paul Newcome

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)