Using INDEX/COLLECT with an OR statement?

Jordan1990 · 03/01/22

I'd like to pull a value from another sheet based on a cell value in my current sheet Matching either 1 of 2 columns in my other sheet.

INDEX(COLLECT({Range where value I want to pull is located}, {Column 1 where I'd like to Match cell with}, @cell, OR({Column 2 where I'd like to Match cell with}, @samecell),0))

This way, if @cell has a match of either column 1 or column 2, it pulls the value from the specified column in the beginning of my formula.

Now from what I understand about the OR operator is that it works best with definitive statements for single cells, but I need to match based on the contents of 1 of 2 column ranges.

Thanks.

Jeff Reisman · 03/01/22

@Jordan1990

Nevermind, the below will not work!

Essentially, by using an OR you're really only giving it one criteria to match, so an INDEX/MATCH may work better here. I have no idea if this will work though! Try placing both your Critera range / criteria pairs for the OR inside the OR, separated by a comma:

INDEX({Range where value I want to pull is located}, MATCH([CellThatIWantToMatch]@row, OR({Column 1 where I'd like to Match cell with}, {Column 2 where I'd like to Match cell with}), 0))

Jordan1990 · 03/02/22

@Jeff Reisman no worries! I think what I'll have to do is create a helper column which concatenates the two columns I am checking in, and then incorporate the Contains function with the cell that I want to match

Jeff Reisman · 03/02/22

https://community.smartsheet.com/discussion/comment/321253#Comment_321253

That's a good plan! I am a big fan of helper columns for stuff like this!

Using INDEX/COLLECT with an OR statement?

Answers

Categories