CODEWITHSUNDEEP
arraysexcelindexingexcel-formulamatch
Manzar Ghansar
Manzar Ghansar

Reputation: 11

Index match function with multiple criteria and duplicate values

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"

Upvotes: 0

Views: 12095

Answers (1)

Gravitate
Gravitate

Reputation: 3062

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))

enter image description here

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:

enter image description here

=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:

enter image description here

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.

enter image description here

=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.

Upvotes: 1

Related Questions