Excel Index Match Find Row Where Column Meets Criteria and Greater than 0-CodePudding

I have a data set in Excel that represents names and job IDs on the Y-axis, and days of the week on the X-axis. Occasionally a name will float to other job IDs but a name will never have more than one job ID per day. I am attempting to find the job ID while inputting the name and day of the week, finding the job with a value > 0.

Example

So far, I am using =INDEX(B2:G9,MATCH(J2,A2:A9,0),MATCH(J3,C1:G1,0)) with the simplified example, but this always returns the first match, and I am not sure how to narrow it down to the row having value > 0. In the example above, I am expecting G1 for Job ID.

CodePudding user response：

Below you can find a working formula (though not too elegant):

=INDEX(B2:B9,MATCH(1,(J2=A2:A9)*(0<IFS(J3=C1,C2:C9,J3=D1,D2:D9,J3=E1,E2:E9,J3=F1,F2:F9,J3=G1,G2:G9)),0))

Here is the refernce for the index-match with multiple criteria.