INDEX COLLECT formula when the Criterion contains more text than Criterion Range

Stephanie K

Hello,

I have a formula that works correctly to index "Obser 1" text when there is only one "Vendor #" in the Criterion cell and there is an exact match to the "Vendor Number" in the Criterion Range column.

=IFERROR(INDEX(COLLECT({Obser 1}, {Vendor Number}, $[Vendor #]$1, {Vendor Location}, $Location$1), 1), "")

I need help with a formula for when the Criterion cell contains multiple Vendor # values but the Criterion Range only contains one or two of the values.

Sheet 1 with Index formula, Criterion cell value:

Sheet 2 where "Obser 1" data is indexed, Criterion Range column:

How can I write an Index formula when there are multiple Vendor #s in the Criterion, and the values in the Criterion Range contains any text matching Vendor Numbers? (Would only need 1st row found Index return.)

I tried to change the columns to multiselect dropdowns, that didn't remedy the issue, but it could have still been a bad formula.

Any help is appreciated,

Thank you!!

KPH

The HAS function is designed to allow you to have one thing in one cell and match it onto a multi-select that contains that one thing and others.

The #INVALID VALUE error means the formula contains a number outside of the range that a function's argument expects. 

To troubleshoot, does this work?

=INDEX(COLLECT({Obser 1}, {Vendor Number}, {Vendor Location}, $Location$1), 1)

KPH

HI

You were on the right track with multiselect dropdowns. If you do that you can use the HAS function within the criteria for your INDEX COLLECT, like this:

=IFERROR(INDEX(COLLECT({Obser 1}, {Vendor Number}, HAS(@cell, $[Vendor #]$1), {Vendor Location}, $Location$1), 1), "")

This will now return the value from the first row in Obser 1 that has the number in Vendor # in the Vendor Number column.

Stephanie K

Thank you for the formula suggestion. Unfortunately, this did not work and is returning a "#INVALID VALUE" error. (I removed the "IFERROR" part of the formula, so the return was not blank.)

I did update the columns on both sheets where vendor number data is entered to multi-select drop down type, but this did not resolve the error.

I have a feeling that my issue is the "HAS" criterion value cell contains 3 drop down numbers, and the criterion range cell only has 1 drop down value. So even though the criterion range is one of the 3 numbers, for some reason I'm still getting an error return.

Here is the formula I tried where I'm getting the error and not the expected return.

=INDEX(COLLECT({Obser 1}, {Vendor Number}, HAS(@cell, $[Vendor #]$1), {Vendor Location}, $Location$1), 1)

I'm open to other ideas or if there is an error in the formula, please let me know.

Thank you!

KPH

The HAS function is designed to allow you to have one thing in one cell and match it onto a multi-select that contains that one thing and others.

The #INVALID VALUE error means the formula contains a number outside of the range that a function's argument expects. 

To troubleshoot, does this work?

=INDEX(COLLECT({Obser 1}, {Vendor Number}, {Vendor Location}, $Location$1), 1)

Stephanie K

Hello!

I went back and started from scratch and the formula below is now functioning as expected!

=INDEX(COLLECT({Obser 1}, {Vendor Number}, HAS($[Vendor #]$1, @cell), {Vendor Location}, $Location$1), 1)

I must have had a typo somewhere.

Thank you KPH for your help! Making the columns multi-select was key and I'm so relieved that I'm able to use this formula to accomplish the task at hand. I appreciate your contribution to the Smartsheet community!

KPH

That's great. Thanks for letting me know.