Index Match based on two criteria from multiple columns

Laura Bartlett

Hello

I am trying to set up a sheet reference based on two criteria (Center and Department). I have tried the index/match and that did not work, so I tried multiple variations of the index collect-- but that did not work either.

See image for the sheet i want to populate the data and the reference sheet that I have the data stored.

Ideally, it would function like this-- form user selects Center and Department and based on those columns, the correct people are populated.

Thank you in advance!

Paul Newcome

Do you have the center listed in the reference sheet as well?

Kaveri Vipat

Hi Laura Bartlett 

As I am assuming that your other sheet also has a Center column so according to my understanding I have Created a Formula, created a unique key of (Center & Department) in both the sheets (=Center@row + Department@row), and then applied Index/match formula, so my formula is - =IFERROR(INDEX({IPO}, MATCH([Unique Key]@row, {Unique Key}, 0)),””). 

If this is not useful to you, please provide other information and the formula as well. that you are currently trying to apply to achieve this.

Have a Good Day! 

Laura Bartlett

Yes, the column names -- IPO, SWI and PCI.. I apologize,

I forgot that no one would recognize those 3 location abbreviations :)

Paul Newcome

Taking a second look... It looks like the Centers are the columns in the reference sheet. In that case, something like this should work for you:

=INDEX({IPO:PCI Columns}, MATCH(Department@row, {Department Column}, 0), IF(Center@row = "IPO", 1, IF(Center@row = "SWI", 2, 3)))

Paul Newcome

@Laura Bartlett It looks like we were typing at the same time. Haha.

Have a look above and let me know if that works for you.

Laura Bartlett

Thank you Kaveri, I have another project to tackle today so I won't be able to work on this one. But I will get back to you.

Just so I know the right track-- i should create a unique ID on the reference sheet and then index match that new ID?

Paul Newcome

@Laura Bartlett That solution only works if you have the Centers also listed in their own column on the reference sheet. Since you are using the structure that you have where each center is its own column, you are going to want to try something along the lines of what I suggested above.

Laura Bartlett

HI Paul

I was not able to get your formula to work. Here is a breakdown of where it is pointing

=INDEX({Center}, = the 3 center columns on my reference sheet (IPO, SWI, PCI)

MATCH(Center@row, {Department1}, 0), = the department name column

IF(Center@row = "IPO", 1, IF(Center@row = "SWI", 2, 3))) = i was not sure how this part was working

Paul Newcome

Are you getting an error or an unexpected output?

The IF statement goes in the 3rd portion of the INDEX function. The third portion of the INDEX function determines which column number to pull from (similar to the match function indicating which row number to pull from).

So if Center@row = "IPO", the INDEX function will pull from the 1st column in your reference sheet which is the IPO column. If Center@row = "SWI" it will pull from the second column which is the SWI column. And if it is neither of those, it will pull from the third column which is the "PCI" column of the reference sheet.

EDIT: I also see that you are trying to match the center in the department column. The MATCH function should be looking for the [Department Column]@row in the {Department Range}.

Laura Bartlett

I'm not sure where I am going wrong.

Here is my formula

=INDEX({Centers}, MATCH(Department@row, {Department}, 0), IF(Center@row = "IPO", 1, IF(Center@row = "SWI", 2, 3)))

Centers= 3 columns in reference sheet:

Department = Department column on reference sheet.

before my last message I was getting a nomatch error. Now I am getting #unparseable

Paul Newcome

Make sure you are using the column names that are actually in your sheet. I just used Department@row as an example. If your column is actually named something else, you will need to use what it is actually named.

Laura Bartlett

Oh my :( I knew it was going to be some very stupid mistake on my side. And you 100% found it.

Thank you for your help. I have never done a index/match if statement! And thanks to you i now have a new formula to work with :)

Have a wonderful weekend!

Laura Bartlett

https://community.smartsheet.com/discussion/comment/378064#Comment_378064

https://community.smartsheet.com/discussion/comment/378064#Comment_378064

Hi Kaveri, I used/learned you option too! I now have to ideas to take back to my user! Thank you!

Paul Newcome

Happy to help. 👍️