Need to update a formula to match items from multiple columns

gwilkinson

I have a project currently piloting to update census numbers in animal cages. The formula I have pulls from another sheet and populates the data into the rooms of the same name. The problem I have discovered is there are some spaces that have two cage types we track, but my formula does not account for this.

This is what is in the source sheet.

The current formula:
=IFERROR(INDEX({Census Admin Range 1}, MATCH([Bldg-Room]@row, {Census Admin Range 2}, 0)), "")

This is what the formula populates to the destination, Basically it is putting the number from the "Oblong" cage type in both.

This formula grabs the census numbers from the source sheet and populates them into the column of the target sheet based on [Bldg-Room0. Due to the extra layer I also need to match [Species/Cage/EO] I tried the following which did not work.

=IFERROR(INDEX({Census Admin Range 1}, MATCH([Bldg-Room]@row + [Species/Cage/EO]@row, {Census Admin Range 2}, 0)), "")

I tried a couple other variations and I got a message saying smartsheet could not MATCH to multiple columns?

I feel like this is so easy I am missing what's right in front of me or it isn't possible…

Thanks for any suggestions.

Kerry St. Thomas

I won't lie. The more complex it gets, the more likely it is I get lost in all the INDEXing and MATCHing. I have a workaround that I think will work in your case. SUMIFS.

=SUMIFS( NumberColumnRange, Bldg-RoomRange, Bldg-Room@row, CageTypeRange, CageType@row)

I'm unsure what your columns are called, but I gave it my best shot.

I see you've also got your formula wrapped in an IFERROR, so you'd want to make a determination how you want it to show up in your individual use case. If this works for you, then great! Good luck and happy Smartsheeting!