INDEX MATCH is not pulling correct data even when formula is correct

Sarah_lee123

Hello, for some reason I am having a recurring problem. Well, 2 recurring problems in the same vein.

I am trying to pull in data from one sheet to another using index/match. I am positive that this formula is correct because I use it about 100 times throughout all of my sheets. In this particular sheet, this formula is not pulling in the data that is clearly in the source sheet, but not populating the receiving sheet.

This has happens multiple times. Sometimes I think I fixed it, then I come back another day and its is not pulling again.

I am perplexed about:

1) Why this info isn't pulling in (is it because of the apostrophe in the barcode number?), and

2) SOMETIMES when I go to correct the formula, the receiving sheet doesn't seem to remember its reference source sheet and it is requesting that I re-map it.

This is going to be a big problem if my client needs to relink all the columns back to the source sheet because it forgets where it's pulling data from.

I'm not even sure if this is a question for the community or for Smartsheet tech support, but I am posing it here first to see if anyone else has experienced this.

Screenshots below

Note: SOURCE SHEET: This barcode and the number in the "TOTAL UOM's RECEIVED" column. There is a number in the cell.

RECEIVING SHEET: Same barcode, with a "0" in the cell that should read "128"

My formula in the receiving sheet is as follows. I am add in all of the QTY's that appear in the ITEMS RECEIVED source sheet when it matches the bar code. Since 128 were received and none were removed (different sheet), this cell should read 128 when this formula is applied.

This seems to be happening with the barcodes that are numbers only (and these numbers have an apostrophe in front at the source level which is what I think is causing my problem)

This does not happen with the other barcodes that are alphanumeric.

Thanks a ton for your help. I feel like I ran into this problem before and can't remember the outcome, but I think there is some kind of add-on to my formula that is necessary.

Kelly Moore

Hey @Sarah_lee123

Try this in your SUMIFS.

This looks for the apostrophe, CHAR(39), using ASCII nomenclature, which equals an apostrophe. I used this because otherwise it's hard to see the apostrophe nested in the middle of two sets of double quotes. You could write it as "'" if you wanted. The formula looks for an apostrophe if it's there and substitutes a blank, which means all that remains is BARCODE. If no apostrophe it will just search the data for the BARCODE as normal.

=SUMIFS({UOM RECEIVED}, {BAR}, IFERROR(SUBSTITUTE(@cell, CHAR(39), "") = BARCODE@row, BARCODE@row))

Does this work for you?

Kelly

Sarah_lee123

Thanks @Kelly Moore

That worked like a charm!

Sarah_lee123

@Kelly Moore

Running into this same problem with an INDEX MATCH formula and I'm not sure where to fit the IFERROR message into this one.

=IFERROR(INDEX({INVENTORY UOM}, MATCH(BARCODE@row, {BARCODE}, 0)), "")

Need to add IFERROR(SUBSTITUTE(@cell, CHAR(39), "") = BARCODE@row, BARCODE@row))

Thanks again for your help!

Kelly Moore

Hey

Let's try switching to an INDEX/COLLECT so you have more flexibility in criteria.

=INDEX(COLLECT({INVENTORY UOM},  {BAR}, IFERROR(SUBSTITUTE(@cell, CHAR(39), "") = BARCODE@row, BARCODE@row)),1)

Does this work for you?

Kelly

Sarah_lee123

that did work, thanks!

Is there a place I can look up the differnece between index/match and index/collect? I don't know the difference between the two.

Kelly Moore

Hey Sarah

You can find references to the specific functions Index, Match and Collect in the Learning center but I'm not sure of place that explains the difference between Index/Match and Index/Collect exactly. The COLLE.CT is a very powerful function and is probably the one I use most frequently- it can be paired with so many other functions.

An Index/Collect can almost always be used - similar to how COUNTIFS and SUMIFS can always be used vs the singular COUNTIF and SUMIF. The Index/Match is typically more restrictive, similar to how restrictive COUNTIF and SUMIF are (which is why, personally, I never use COUNTIF and SUMIF). As a rule of thumb, I use Index/Match when I have one criteria I am going to match. If there are multiple criteria - or if the criteria is complex, I will switch to an Index/Collect. I stress though this is a rule of thumb and one can make the Index/Match more complex. You'll get a feel for these as you progress with formulas.

Maybe someone else in the community will chime in and offer a better explanation

Kelly

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

https://help.smartsheet.com/function/collect