Index match function with multiple criteria and duplicate values

Question

I am trying to work my way out using index and match function (I am relatively new using this function) I have a database with multiple criteria and also has duplicate values. I have 3 criteria for index and match Salesman Name (duplicates in the field), month and value greater than 0. I have to select stores names based on the name of salesperson, month and value.

Outlet Name       salesman Name     Month       Value
Outlet ABC           Tom             Jan         1
Outlet BCD           Tom             jan         2
Outlet XYZ           Marc            Feb         1
Outlet UTR           Tom             Mar         0

How can I use Index match function to select salesman "Tom" month "Jan" & value > 0

Note: Month, Salesman are fixed each row "to enter the salesman name & Month - input fields" however the value criteria is fixed at ">0"

Which outlet would you like it to select in the case of "Tom" and "Jan"? — Gravitate
– Gravitate, Commented Jan 22, 2019 at 11:33
its all the outlets in the above case. All the stores that meet the above criteria should be selected — Manzar Ghansar
– Manzar Ghansar, Commented Jan 23, 2019 at 11:05

Gravitate · Accepted Answer · 2019-01-23 13:17:09Z

1

Assuming that you just want to select the first outlet in the case of duplicates...

You can use INDEX/MATCH/INDEX to select based on multiple criteria:

=INDEX($A$2:$A$5,MATCH(1,INDEX(($B$2:$B$5=$F2)*($C$2:$C$5=$G2)*($D$2:$D$5>0),0),0))

This is essentially the same as a normal INDEX/MATCH, however, it uses a second INDEX to find the index where all the required criteria is true.

If you would like to return both outlets in the case of duplicates, I would suggest adding a helper column ("Outlet ID") and use a MINIFS formula to select each. Let me know if you need this suggestion clarifying.

EDIT: UPDATE BASED ON FURTHER COMMENTS

If I understand correctly, I think that you would like a table where you can input the salesman and month, and it spit out a list of Outlets where the value is >0.

Based on this, I think that your best bet would be to add a helper column to the list of data. This helper column with give each distinct outlet a unique ID:

=IF(
    MINIFS($A$2:$A2,$B$2:$B2, $B3)=0,
    MAX($A$2:$A2)+1,
    MINIFS($A$2:$A2,$B$2:$B2,$B3)
)

This formula will check if an ID has already been assigned to the current outlet. If it has, it will use that one. If it hasn't, it will check what the MAX ID used so far is, and add 1 to it.

Now, we can use these IDs to produce a dynamic list of IDs associated with a particular Salesman/Month/Value:

The main formula here is the:

MINIFS($A$3:$A$7,$C$3:$C$7,$G$3,$D$3:$D$7,$H$3,$E$3:$E$7,">"&0,$A$3:$A$7,">"&MAX($G$6:$G6))

The rest is just to remove the zeros which are returned when we do not have any more IDs in out list.

The MINIFS formula selects the lowest ID where the salesman is the one we want AND the month is the one we want AND the value is >0 AND the ID is greater than any which already appear in our list.

Once we have our list of IDs, it is a simple matter to retrieve the name of the outlets using an INDEX/MATCH. We can then hide the IDs entirely if we wish.

=IFNA(INDEX($B$3:$B$7,MATCH($G7,$A$3:$A$7,0)),"")

The IFNA is just to prevent errors showing on empty lines. If you preferred, you could do something like this instead:

=IF($G7="","",INDEX($B$3:$B$7,MATCH($G7,$A$3:$A$7,0)))

Please let me know if you need me to expand any more.

edited Jan 23, 2019 at 13:17

answered Jan 22, 2019 at 11:45

Gravitate

3,0722 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Manzar Ghansar Over a year ago

Thanks for your help. The issue in this case is the duplicates are with salesman name, since one salesman will cover multiple stores and not with stores. I guess helper column will do. however it would be awesome to get it without it.

Manzar Ghansar Over a year ago

Also i would like to get a list of all the stores which meets all the 3 criteria. I have tried using small & row function for this but I am not able to add 3 criteria, just managed one "salesman name"

Gravitate Over a year ago

@ManzarGhansar I am not really sure what you mean? Do you mean, if you enter "Tom" & "Jan", it would return both "Outlet ABC" and "Outlet BCD"? Do you need it to be in a table format (like in my answer, but with an additional row for "Tom/Jan/Outlet BCD"? Or just a dynamic list of Outlets when you enter a salesperson and Month?

Aishwarya Borkar · Accepted Answer · 2025-07-20 18:42:02Z

0

enter image description here i have solved most of the complex scenarios using FILTER Function here is the explanation which i have given step by step

(1.)First find the MAX-You will get the MAX number

(2.)now this MAX number at what position is present we will use SEARCH from that array.

(3.)This SEARCH will definitely give an error where it doesn't find this number from that array and a number(position) where it finds.

(4.)So obviously the FILTER function accepts Boolean data in the include argument,so we will turn this SEARCH into Boolean by ISNUMBER.

(5.)So this will convert into TRUE/FALSE.

(6.)now what we want to filter?

subjects right?

So Filter the subjects where there is TRUE

answered Jul 20 at 18:42

Aishwarya Borkar

1

Collectives™ on Stack Overflow

Index match function with multiple criteria and duplicate values

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related