result with index/match but not index/collect

Christina09

Hi,

I have a formula with multiple conditions, so I used index/collect to pull the data. However, that didn't pull the data.

Basically, if the ID# in sheet A matches sheet B and the details column in sheet A is not blank, it should pull the details in sheet A.

=IFERROR(INDEX(COLLECT({details}, {ID#}, [ID#]@row, {details}, <>""), 1), "")

Should be pretty straightforward forward right?

So I tried index/match: =IFERROR(INDEX({deails}, MATCH([ID#]@row, {ID#}, 0)), "")

which of course, it pulls the data. But I need more than one criteria, so index/match isn't my solution.

Does anyone know why it wouldn't pull the data with index/collect?

Christina09

@Paul Newcome I finally figure out how to populate the data. Instead of =[ID#]@row+"", it has to be =VALUE([ID#]@row in the helper column. I'm not sure why the ID# that starts with 0 has such a big impact that it wouldn't populate.

Paul Newcome

Try removing the IFERROR from both and see what the output is.

Christina09

@Paul Newcome #INVALID VALUE for index/collect. index/match works fine, it pulls the data.

Paul Newcome

If it works for the MATCH but you get that error with the COLLECT then that means it is not finding a match on the ID that isn't blank. Think of that error as the equivalent to #NO MATCH when using the INDEX/COLLECT.

Try applying a filter to the sheet that singles out the one of the IDs that should be populated and where the Details column is not blank. If it pops up with at least one row in the source sheet, then the INDEX/COLLECT should work for that one.

Another option would be to (temporarily) test it using a COUNTIFS with the same range/criteria sets of matching on the ID and the Details range not being blank.

Christina09

@Paul Newcome I tried using COUNTIFS with blank and without blank in the formula, it outcome is still 0.

Not sure if it matters, but some of the ID# start with 0. I created a helper column in both sheet A and sheet B to covert it to a txt =[ID#]@row+""

Paul Newcome

So it sounds like it is having an issue with the ID. The leading zeros will cause that issue since those without would be stored as a number and those with the zero would be stored as text. Did it work when you added the helper columns and referenced those since you are now comparing all text to all text?

Christina09

@Paul Newcome even referencing the helper column, didn't help. I couldn't figure out what could be the problem.

Paul Newcome

The formulas in your original post... Did you copy/paste those directly from the sheet?

Christina09

@Paul Newcome it's pretty much copy & paste, I only changed the sheet and column name

Paul Newcome

The reason I ask is that I notice your INDEX/COLLECT uses {details}, but your INDEX/MATCH uses {deails} (without the "t" in the middle). I was wondering if you could have accidentally set up two different ranges without realizing it.

Christina09

@Paul Newcome I finally figure out how to populate the data. Instead of =[ID#]@row+"", it has to be =VALUE([ID#]@row in the helper column. I'm not sure why the ID# that starts with 0 has such a big impact that it wouldn't populate.

Paul Newcome

That's odd that it works that way but not when you convert it all to text. I have always done the text conversion

[Column Name]@row + ""

with no issues before.

I am glad that it is finally working for you now though.