Need help with retrieving multiple values in the order they appear in the array

[Simonpyman]

Hello,

I have explored different functions (VLOOKUP,INDEX,MATCH) but i cannot figure how to retrieve multiple values for the same cell name. In this case most of the cell names have duplicates with the exception of the cell 17 field. Cell 17 has 4 cells with corresponding values (57,58,58 and 55) but i cannot figure out how to display them in order that they appear in the light blue cells below (cell input data). I can only get the value 57 to appear using the formula that i created from an array of all the cell labels and associated values.

Example of the formula i have used so far:

=INDEX($F$106:$F$201,MATCH("NC",$F$106:$F$201,0)+1,1

Thanks in advance!

 

[Amit Tandon]

I have office 2016 but my company also subscribes to microsoft 365 so i guess that is why i don't need to use ctrl shift enter?

Ultimately i need to add a second set of data as shown in the example below (copied from your file with added columns).

Sorry for the repeated questions! i really appreciate the help. I am just unsure why i cannot enter the formula as shown and get the desired result.


View attachment 15865

To enter an Array formula, select cell where the formula has been entered, press the F2 key, and then press these 3 keys simultaneously - Ctrl + Shift + Enter.

You will need to enter the above formula as an Array formula irrespective of the excel version (2016 or 365 or any other).

Once you have the formula working its easy to calculate the additional columns, ex. use the AVERAGE function to get the average of the values etc. You may confirm thereafter.