Cross Reference and Multiple Columns

Matt Desjardin

Hello Smartsheets!

Trying to find a solution when matching rows across sheets. Is it possible to say, if multiple criteria in columns match criteria in multiple columns on another sheet to check a box? For example, if I have identical rows on two sheets (sheet A and sheet B) of matching information, check a box on sheet A.

Let's say I want to check if my vendor column, item column, and item number are a match across two sheets. Can I return a value or check a box based on this data? Wasn't quite sure how to start the formula but I imagine INDEX MATCH or INDEX COLLECT perhaps?

Thanks in advance for any advice!

-Matt

KPH

Hi @Matt Desjardin

INDEX COLLECT will return a value from sheet B where the row in sheet B matches the things on sheet A that you specify in your COLLECT function. So, if you have a column in sheet B that is a checkbox or the value you want to return if you find a match, then you would make that the value to be returned in the INDEX COLLECT.

If Sheet B looks like this*:

And Sheet A like this:

Then you can use a formula like this in Sheet A:

=IFERROR(INDEX(COLLECT({Sheet B Check}, {Sheet B name}, [Vendor Name]@row, {Sheet B item}, Item@row, {Sheet B number}, [Item Number]@row), 1), 0)

If Vendor Name, Item, and Item Number on Sheet A match those on Sheet B, then it will return the value from the Check column on Sheet B, on the first row that matches. If no match it will return an error, which the IFERROR function will replace with a 0. So the output would be:

* I used a column formula to check these boxes as soon as the vendor name was entered. You can then hide this column and forget about it.

=IF(ISBLANK([Vendor Name]@row), 0, 1)

Is that the sort of thing you are looking for?

KPH

Hi @Matt Desjardin

INDEX COLLECT will return a value from sheet B where the row in sheet B matches the things on sheet A that you specify in your COLLECT function. So, if you have a column in sheet B that is a checkbox or the value you want to return if you find a match, then you would make that the value to be returned in the INDEX COLLECT.

If Sheet B looks like this*:

And Sheet A like this:

Then you can use a formula like this in Sheet A:

=IFERROR(INDEX(COLLECT({Sheet B Check}, {Sheet B name}, [Vendor Name]@row, {Sheet B item}, Item@row, {Sheet B number}, [Item Number]@row), 1), 0)

If Vendor Name, Item, and Item Number on Sheet A match those on Sheet B, then it will return the value from the Check column on Sheet B, on the first row that matches. If no match it will return an error, which the IFERROR function will replace with a 0. So the output would be:

* I used a column formula to check these boxes as soon as the vendor name was entered. You can then hide this column and forget about it.

=IF(ISBLANK([Vendor Name]@row), 0, 1)

Is that the sort of thing you are looking for?

Matt Desjardin

@KPH Thank you for the detailed response! Yes I think this is what I'm looking for. I didn't think about using the check box as the value to return, this makes a lot of sense. I'll try to incorporate this into what I'm doing, I really appreciate your time!

KPH

No problem Matt. I've not seen it done like this, but couldn't think of a simpler way. Let me know if you run into any issues.

Matt Desjardin

@KPH Worked exactly how I needed it too, thanks again!

KPH

Wonderful, thanks for letting me know.