=INDEX(COLLECT( Formula assistance

System

This discussion was created from comments split from: How can I pull data from another sheet using Index(Collect?.

PDunn

@Lucas Rayala I am trying to do something similar. But when my column doesn't match the formula is populating the cell with the row 1 data. My formula works when the package ID & package # match, but isn't working when there isn't a matching package # on Sheet 2, I would want those to reflect "No Match" by adding a IFERROR

I want to: match column package ID in Sheet 1 to column packaged # in sheet 2, then bring back the sellable SKU from Sheet 2 and place it into Room bundle exists column in Sheet 1, if no match use IFERROR "No Match".

=INDEX(COLLECT({sheet 2 selsku}, {sheet 2 pkgid}, [sheet 1Package ID]@row), 1)

Lucas Rayala

@PDunn, if you go to sheet 2 and filter your sku column by 11502U1, what is the package number? If it’s blank, then that would seem to match your Sheet 1 (blank) package ID. You could try adding additional criteria to your COLLECT to prevent this, such as:

=INDEX(COLLECT({sheet 2 selsku}, {sheet 2 pkgid}, [sheet 1Package ID]@row, {sheet 2 pkgid}, <>””), 1)

Does this help?

PDunn

I want to achieve: when there is a package ID on sheet 1 and package ID on sheet 2 to then place the corresponding Sellable SKU into sheet 1 or if there isn't a matching package ID on both sheets the Sellable SKU should be blank or state No Match when there isn't a corresponding package ID.

Can you explain why I would need to add the , 1) in the above formula? This part of the formula is then placing the row 1 sellable SKU when there isn't a matching package ID this isn't what I am trying to achieve.

PDunn

@Lucas Rayala @Paul Newcome I still have not been able to get this formula to work to get the correct result when there is no matching package # on sheet 2. Any additional insights you can provide would be great thanks.

Lucas Rayala

Hi @PDunn — I've been out for a couple of weeks so sorry for the late response. The addition of this criteria should have eliminated any results where the package ID is blank:

{sheet 2 pkgid}, <>""

Note that you have to actually go through the process of creating the cross sheet reference for {sheet 2 pkgid}, you can not simply type it in.

In the above criteria, the <> indicates "does not equal" and the empty quotes indicates blank, so you are saying, provide the results only if the package ID is not blank. Alternately (and this is my general preference), in place of the <>"", you can put LEN(@cell)>0. The LEN function counts the number of characters in a cell. By adding this criteria, you are looking specifically for results where the package ID contains 'greater than" zero characters, or is not blank. I use this convention because it works universally, whereas the <>"" convention gets tripped up when you point it to a column with a formula.

Regarding the ",1)" — the COLLECT function gathers information specified by the criteria you provide. The COLLECT function could collect 100 results, but in many/most cases, people use it to specify a single result. The INDEX function is a simple range/row lookup, i.e. INDEX(range, row). When you use the COLLECT function as the INDEX 'range', the range becomes the results pulled in by the COLLECT function. Think of the COLLECT results as a single virtual column, with each COLLECT result entered on its own row. If there is only one result, the virtual column has only one row. Because the row is a required input of the INDEX function, and because you expect your COLLECT function to return one result, you need to enter "1" so the INDEX pulls the first (and only) row. That's why you see the ",1)" in the formula.

Your INDEX can't pull in something not specified by the COLLECT range, so you need to check to make sure you have created your cross-sheet reference appropriately. You may want to just start over in a new column to keep things clean.

If you don't want to pull responses when the source sheet does not have an ID, just use an IF statement to specify that. The entire formula could look something like this:

=IF(LEN([sheet 1Package ID]@row)=0, "", IFERROR(INDEX(COLLECT({sheet 2 selsku}, {sheet 2 pkgid}, [sheet 1Package ID]@row, {sheet 2 pkgid}, LEN(@cell)>0), 1), "No Match"))