Return values/text from one sheet into multiple sheets by comparing three columns

1. The layout... with merged cells and blank rows is an Excel disaster. Do you REALLY need them? Can I sue an Excel-friendly alternative?

2. Where do the numbers in columns C & D in the result sheet come from?

1. OK. You do NOT need the blank cells, if you use the correct formula.

2. I figured that out eventually!!

3. I assume it's an error that the ID 69464/0 is used twice... once for a banana category and once for an apple category. i changed it.

4. In the file... different shading = different formula. They KEY one is in column B:

=IFERROR(INDEX(Calculation!C:C,MOD(AGGREGATE(15,6,COUNTIF(Calculation!$B$2:$B$13,"<"&Calculation!$B$2:$B$13)*10^6+ROW(Calculation!$B$2:$B$13)/(Calculation!$A$2:$A$13=$E$1),ROWS(B$4:B4)),10^6)),"")

This sorts the data for John into alphabetical order (based on the category... apple , banana, etc) so all apples are grouped together.

5. Then either change the name in E1 (dropdown box??) or replicate the sheet (a bit pointless??) for other people in the group.

It is not an array formula. Try it with just ENTER.

For some reason that I do not understand... O365 sometimes puts the {} around ordinary formulae when the file is opened in an earlier Excel version. It ALWAYS/MOSTLY happens with SUMPRODUCT... perhaps this is another "feature" of Excel.