I have data in an Excel sheet that is structured with a main category, and sub categories (persons) on the next column in the row below the main category and in the columns next to the sub categories I have qty measurements, see picture for clarification.

I need to do a lookup (for example xlookup or index/match) and find a specific category (yellow), and then find a specific person in that category (yellow) and return the qty numbers (green).

I've looked into multiple criteria look ups with index/match but the criteria's, as far as I understand it, need to be located on the same row for that to work. Another solution might be too find the Main category, and then return that array the 4 rows beneath it.

CodePudding user response：

You can use INDEX/MATCH for first quantity:

=INDEX(D:D;MATCH(1;($C:$C=$I$2)*(ROW($A:$A)>MAX(ROW($A:$A)*($A:$A=$H$2)));0))

for second - copy formula to the right. In pre O365 formula must be entered as array formula with Ctrl Shift Enter