Incorrect Argument Set when using HAS in an INDEX(COLLECT

Nick Burrus

Has anyone had much success using a HAS in an INDEX COLLECT?

I'm trying to get something like this to work:

=INDEX(COLLECT({ITEM ID}, {TONER COLOR}, [Toner Color]@row, HAS({MODEL}, [Xerox Model]@row), 1))

In one sheet I have the following columns:

• ITEM ID
• TONER COLOR
• MODEL

The models have multi value selects. Look at this:

In another sheet I am collecting the following data:

• Model #
• Toner Color

And I want to RETURN the item ID. However, as you may see above, there's a lot of model numbers in one field in a multi-select. I prefer to do it this way as the ITEM ID is repeated for 20 models.

I tried searching and I'm having issues with HAS and it returns INVALID ARGUMENT SENT

Carson Penticuff

Try this one:

=INDEX(COLLECT({ITEM ID}, {TONER COLOR}, [Toner Color]@row, {MODEL}, HAS(@cell, [Xerox Model]@row)), 1)