INDEX MATCH 2 Column Criteria [Partial Match + Full Match]

Vanny 0
Viewed 45

There is a sheet called "Bookings".

Column H reflects their role

  • SALES
  • RECEPTIONIST
  • SALES AND RECEPTIONIST

In the current sheet:

  • Column J, it is a Qualification role e.g. "SALES" or "RECEPTIONIST".

How can I amend the following code on index match to pull over only staff from "Bookings" who partial match Column J?

If someone's role is "SALES", I can get it to pull over "SALES". If someone's role is "RECEPTIONIST", I can get it to pull over "RECEPTIONIST". However, if it is "SALES AND RECEPTIONIST" as the role, it fails to pull over whether it is "SALES" or "RECEPTIONIST".

Please advise how I should amend the Formula below.

=IFERROR(INDEX(Bookings!$B:$B, 
MIN( 
IFERROR(MATCH(1,($N3=Bookings!$C:$C)*($J3=Bookings!$H:$H),0),1E+99), 
IFERROR(MATCH(1,($N3=Bookings!$D:$D)*($J3=Bookings!$H:$H),0),1E+99), 
IFERROR(MATCH(1,($N3=Bookings!$E:$E)*($J3=Bookings!$H:$H),0),1E+99), 
IFERROR(MATCH(1,($N3=Bookings!$F:$F)*($J3=Bookings!$H:$H),0),1E+99), 
IFERROR(MATCH(1,($N3=Bookings!$G:$G)*($J3=Bookings!$H:$H),0),1E+99)

 )))
excel-formula google-sheets excel
1 Answers

What I understand from your statement (your question needs some clarification):

If someone's role is "SALES", I can get it to pull over "SALES". If someone's role is "RECEPTIONIST", I can get it to pull over "RECEPTIONIST". However, if it is "SALES AND RECEPTIONIST" as the role, it fails to pull over whether it is "SALES" or "RECEPTIONIST".

Your are looking for a filter condition that considers an OR condition in case your are looking for both roles. Here is how you can do it on cell F3 (see the screenshot below):

=LET(result, FILTER(C3:D20, 
  (C3:C20<>"") * (IF(A3="SALES AND RECEPTIONIST", 
  (D3:D20="SALES") + (D3:D20="RECEPTIONIST"), D3:D20=A3))), 
  IF(A3="","", result))

You can consider more than one filter condition via FILTER function. Simulating AND condition with multiplication (*) and OR condition with addition (+). Each condition evaluates to 0 or 1, for the same range of the first input argument of FILTER, so this is how you achieve it. I excluded empty rows to consider a more general case. The LET function is used for easy reading. Here is the screenshot:

sample excel file

In the cell A3 I have a list (Data->Data Validation->Allow with the option List) with the following values: SALES,RECEPTIONIST,SALES AND RECEPTIONIST, so you can dynamically filter by your roles.

I hope it helps to use it on your real problem.

David Leal0
Related