Formula not working on new sheet but same data
So I am trying to pull the Tag (Column is named "TAG" and is a 4 digit #) and Loc (Location - Column is named "LOC") from a reference sheet "Perfection Yard Check" when the VIN (Vin number, column is named "VIN" on both sheets) on the current sheet matches the VIN on the reference sheet "Perfection Yard Check". I want to do the same thing with the LOC column, pull the location data in the reference sheets "LOC" column if the VIN matches the reference sheet. I tried the AI but that failed and seemed to simple to have worked.
There is a formula that is working on a sheet someone else built, but when I copy to the new sheet and change the years to match what we are using now, I get an "UNPARESEABLE" error:
=IF(COUNTIF({PERFECTION YARD CHECK VIN}, VIN@row) = 1, INDEX({PERFECTION YARD CHECK TAG}, MATCH(VIN@row, {PERFECTION YARD CHECK VIN}, 0)), IF(COUNTIF({Perfection Yard Delivered 2024 VIN}, VIN@row) = 1, INDEX({Perfection Yard Delivered 2024 TAG}, MATCH(VIN@row, {Perfection Yard Delivered 2024 VIN}, 0)), IF(COUNTIF({Perfection Yard Delivered 2025 VIN}, VIN@row) = 1, INDEX({Perfection Yard Delivered 2025 TAG}, MATCH(VIN@row, {Perfection Yard Delivered 2025 VIN}, 0)), "")))
I feel this formula is more complicated than it needs to be.
Best Answer
-
Never mind - I saw a similar post - you (Paul Newcome) had answered that question as well. I came up with
=IFERROR(INDEX({PERFECTION YARD CHECK TAG}, MATCH(VIN@row, {PERFECTION YARD CHECK VIN}, 0)), "")
and it works perfectly !
Answers
-
The above formula is searching 3 different sheets (and is still more complicated than it needs to be even for that). If you are only searching a single sheet to bring the data over, you only need a single INDEX/MATCH.
=INDEX({Column To Pull Over}, MATCH(VIN@row, {VIN Column}, 0))
-
Thank you for the prompt reply ! Much appreciated. I am still not getting something correct. I do see in thew formula you supplied you have "MATCH(VIN@row," - all of my data is sorted by column that I am trying to reference. Not sure if that's the issue. I will try to be more descriptive. And really, I am not wanting someone to just do it for me, it's just so confusing (until I understand it better) when to use INDEX, when to use COUNTIF or COUNTIFS.. I am pretty good with excel but this advanced stuff if kicking me booty :-)
See the attached photos - on the one that has "flogistix" in the title - we are needing the TAG column to auto populate from the "Perfection" sheet when the VIN numbers match. The VINs will be populated on the Flogistix sheet 1st, when the Perfection sheet gets the VIN added, we want the TAG to auto-populate on the Flogistix sheet. This will help the people on the Flogistix sheet know when a unit has arrived..
Than we have another sheet that we was to do the same thing, but ALSO have the LOC (location) column data auto populate on the Flogistix sheet, same criteria (matching the VIN).
I know this is a lot, but perhaps the correct usage and direct us to the article that will explain..
-
So I figured out a formula that works, exactly as you indicated:
=INDEX({PERFECTION YARD CHECK TAG}, MATCH(VIN@row, {PERFECTION YARD CHECK VIN}, 0))
The only issue is on the cells that do not return a match, these are displaying #NO MATCH in those fields. I am aware in excel the ;;; formatting to return a blank field. Is there something similar in smartsheets ?
Thanks in advance.
-
Never mind - I saw a similar post - you (Paul Newcome) had answered that question as well. I came up with
=IFERROR(INDEX({PERFECTION YARD CHECK TAG}, MATCH(VIN@row, {PERFECTION YARD CHECK VIN}, 0)), "")
and it works perfectly !
Help Article Resources
Categories
Check out the Formula Handbook template!