VLOOKUP Not working

Lauryn Vogt

I have a sheet of all our products for sale. We are introducing new pricing in Jan. 2024. I have the new pricing in a smaller sheet that I imported in from Excel. The first column of that sheet is our Core Stock Number, then the pricing column, for example, Retail, Sale, Qty Discounts, etc. I want to use a VLOOKUP to put the new pricing from the imported sheet into our Current Product Sheet. I am getting the #UNPARSEABLE error. I am unsure what I am doing incorrect. A screenshot of my formula is attached.

I know I will have another issue once I get this to work. We use something like a Parent Child Relationship on our items. For Example, we may have 3 lines in our sheet with the same Core Stock Number, but then a different component. We need the pricing to only be on the "Parent" row. If I insert the VLOOKUP as a Column formula, I am afraid it is going to be filling in pricing on all 3 rows that have the same Core Stock Number and not just our Finished Good - the sellable version. Screenshots are attached. Any ideas on how to write a formula to pull in pricing only on the Finished Good Line when we find a match on the imported pricing sheet?

Screenshot 2023-10-12 Formula.png

Screenshot 2023-10-12 item code.png

Nick Korna

Hi @Lauryn Vogt,

You don't need the square brackets [ ] around false in your lookup - these will definitely cause an error.

If the parent item is listed first, it should capture this item in the lookup. If that isn't the case, you can get round this by using an INDEX/COLLECT instead. Unfortunately I can't really say what this would look like without column headers on the respective sheets.

Hope this helps, but if you've any problems/questions then just post! 🙂

Nick Korna

Hi @Lauryn Vogt,

You don't need the square brackets [ ] around false in your lookup - these will definitely cause an error.

If the parent item is listed first, it should capture this item in the lookup. If that isn't the case, you can get round this by using an INDEX/COLLECT instead. Unfortunately I can't really say what this would look like without column headers on the respective sheets.

Hope this helps, but if you've any problems/questions then just post! 🙂

Lauryn Vogt

@Nick Korna thank you, that did help get my vlookup to work. It does fill in the $$ on the component, "child" lines now as well as on the FG line. I am unsure how to write the INDEX/COLLECT formula. I looked at it in the Smartsheet Formulas Handbook, but am not sure how that should look for my sheets. I have attached some screenshots of column headers. The screenshot called "...New Pricing Header" shows the column in the imported sheet that I am trying to grab the pricing from to link into the Master Sheet of all products. You can see the example of a Component "C" on that sheet that doesn't have $ but it has the same Core Stock Number as the FG line that should have the pricing for that product. The screenshot titled "...Master Sheet" shows the same columns in our full product master. The headings all match. You can see I put the VLOOKUP into the Retail Price column and converted it to a column formula. I need to populate the remaining columns with new pricing. Then our hope is to essentially copy and paste values, removing the formulas in the master sheets since this is a one time bulk price change project.

I ran a report to show items that I updated the pricing for from the vlookup to verify it worked since I just did a small portion until I understand what the formula should look like. You can see from that screenshot that the highlighted items has a component and finished good filled in with the same price and I need it only on the finished good line.

Thanks for the help on this!

Screenshot 2023-10-12 Book Pricing Report.png

Screenshot 2023-10-12 Master Sheet.png

Screenshot 2023-10-12 New Pricing Header.png