CODEWITHSUNDEEP
excelindexingmatchmultiple-columns
Justin Bowie
Justin Bowie

Reputation: 3

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

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.

Upvotes: 0

Views: 820

Answers (2)

Robert Mearns
Robert Mearns

Reputation: 11996

An alternate approach is to use the FILTER and INDEX functions.

Breaking the approach down into separate steps before combining the formulas into a single mega formula. Then offering a simplification of the formula using the LET function.

enter image description here

Filter based on the name.

=FILTER(A1:G9,(A1:A9=J2)+(A1:A9="Name"))

The plus symbol (+) used in include portion of the formula acts as an OR function.
It is used to bring the headings row into the results.

enter image description here

Filter based on day

=FILTER(A12#,(INDEX(A12#,,{1,2,3,4,5,6,7})=J3)+(INDEX(A12#,,{1,2,3,4,5,6,7})="Job Id"))

The results of the previous formula are referred to using a cell reference and a hash (A12#) to select the cell and the spill area.
The INDEX function is used with filter the spill range before applying the criteria. The array {1,2,3,4,5,6,7} refers the seven column headings.
The plus symbol (+) is used again as an OR function.
It is used to bring the Job Id column into the results.

enter image description here

Filter based on hours

=FILTER(A18#,(INDEX(A18#,,2)<>0))

The results of the previous formula are referred to using a cell reference and a hash (A18#) to select the cell and the spill area.
The INDEX function is used to filter the spill range before applying the criteria.

enter image description here

Get the results

=INDEX(A24#,2,1)

Use the INDEX function to retrieve the first column, second row from the results.

enter image description here

Mega formula

Putting all the steps into a single formula.

=INDEX(FILTER(FILTER(FILTER(A1:G9,(A1:A9=J2)+(A1:A9="Name")),(INDEX(FILTER(A1:G9,(A1:A9=J2)+(A1:A9="Name")),,{1,2,3,4,5,6,7})=J3)+(INDEX(FILTER(A1:G9,(A1:A9=J2)+(A1:A9="Name")),,{1,2,3,4,5,6,7})="Job Id")),(INDEX(FILTER(FILTER(A1:G9,(A1:A9=J2)+(A1:A9="Name")),(INDEX(FILTER(A1:G9,(A1:A9=J2)+(A1:A9="Name")),,{1,2,3,4,5,6,7})=J3)+(INDEX(FILTER(A1:G9,(A1:A9=J2)+(A1:A9="Name")),,{1,2,3,4,5,6,7})="Job Id")),,2)<>0)),2,1)

Not very easy to read or maintain.

Use LET

To simplify the formula, use the LET function to calculate each of the steps.
The name portion of the formula is used to clearly identify each step.
If an invalid parameter is used, instead of a #CALC error, the text Invalid Selection is displayed.

=LET(
NameFilter,
  FILTER(A1:G9,(A1:A9=J2)+(A1:A9="Name")),
DayFilter,
  FILTER(NameFilter,(INDEX(NameFilter,,{1,2,3,4,5,6,7})=J3)+(INDEX(NameFilter,,{1,2,3,4,5,6,7})="Job Id")),
HourFilter,
  FILTER(DayFilter,(INDEX(DayFilter,,2)<>0)),
Result,
  INDEX(HourFilter,2,1),
IFERROR(Result,"Invalid Selection")
)

enter image description here

Upvotes: 1

Alessandro
Alessandro

Reputation: 381

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.

Upvotes: 0

Related Questions