my collect function doesnt work

bryanlow

I have used a collect function workaround from one fo the community boards, and it works wonders. however it doesnt seem to work when this new data lookup range. note the data lookup range has been edit with the =left function.

=JOIN(COLLECT({Open Order Inquiry2020-07-07 Range 3}, {Open Order Inquiry2020-07-07 Range 2}, [Primary Column]1), SUBSTITUTE(helper$1, "-", ""))

this is the data range that is being referred to :

Please help

Paul Newcome

Are you getting an error or an unexpected result?

bryanlow

Hi Paul,

i'm getting an incorrect result. for example, in the sheet "inventory c200702" row 460, there is an entry which should appear in the smartsheet "item check" column of 'our stock' row 3.

the data range being referred to from column desc 1 row 460, (1040), is not appearing with the lookup value range of "item check" column of 'our stock' row 3 which is also (1040).

it seems to work when the lookup ranges are both primary columns, as evident in other columns in "item check" sheet.

was wondering why it doesn't work with the other columns when there is clearly a result that should appear.

Paul Newcome

How are the columns "item check [Column9]" and "inventory c200702 [desc 1]" populated?

bryanlow

hi Paul,

for " item check (column 9)" :

for "inventory c200702 [desc 1]" :

Paul Newcome

Hmm... We just had a similar problem (I think it was just yesterday) with another person here in the Community, but now I can't seem to find it.

Let's test each part separately...

Enter this into any text/number cell on your target sheet. You should get a number. That number should be the matching row number in your source sheet...

=MATCH([Primary Column]1, {Open Order Inquiry2020-07-07 Range 2})

bryanlow

Hi Paul,

i took the liberty to edit the formula to =MATCH([Column9]3, {inventory C200702 Range 2})

to test if the smartsheet does recognise the 1040 in the 1040 lookup range. however it seems to return with a "NO MATCH". strange when there clearly is a match. is this a bug due to the "=left" function?

bryanlow

however what is even stranger is it seems to work with somes and produce incorrect answer in others

6363 is present in row 335 (no match)

1040 is present in row 460 (no match)

7759 is present in row 207 (1, correct)

9045 is present in row 310 & 311 (2, correct)

9436 is not present. (2, incorrect. there is no 9436 in the inventory file)

Paul Newcome

Actually none of the MATCH functions are correct. The MATCH function should be returning the first row number that a match is found in.

So if "7759" is in row 207, then the MATCH function should produce 207. If "9045" is in rows 310 and 311 then the MATCH function should return 310.

I think the problem is that I missed a section in the MATCH function to look for exact matches. Sorry about that. Try this one and let me know how it goes...

=MATCH([Column9]3, {inventory C200702 Range 2}, 0)