Index and Match Used in Branching Formulas

Elliot Holder
Elliot Holder ✭✭
edited 01/07/22 in Formulas and Functions

I have a column using an index and match formula to index the cost based on a match of client name. In the same sheet, I have a Total Sample Cost column that sums the Normal Sample Cost with a couple of other fee columns. I did not build the sheet originally and when I went to change the pricing structure to index and match to a new sheet, the Total Sample Cost broke. I cannot figure out how to adjust the formatting to get the Total Sample Cost to include the Normal Sample cost.

  • Original Index and Match: =INDEX({Proposal and Cost Range 1}, MATCH([Pricing PK]18, {Proposal and Cost Range 2}, 0))
    • This linked to a sheet that had a primary key based on several selections. The Indexed cost was a simple text number in each cell.
  • New Index and Match: =INDEX({Vendor Event Cost 1}, MATCH(Vendor2, {Vendor Name 1}, 0))
    • The Index references a new client sheet where there are commitment tiers that determine the sample cost, so it is referencing a cells with formulas.
Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Elliot Holder

    The reason the numbers are showing up as text is due to the quotes around them in that IF statement.

    For example, where you have "$0.50" you'll want to have 0.50

    Then once all the values are numbers in the formula without quotes, you can set the $ sign to the entire column using the formatting toolbar at the top of the sheet.

    Let me know if that makes sense or if you need help adjusting the formula!

    Cheers,

    Genevieve

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Elliot Holder

    I hope you're well and safe!

    I'd be happy to take a quick look.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    I hope that helps!

    Have a fantastic weekend & Happy New Year!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi Andree,

    I've shared a sample version of the vendor list and am attaching some screen shots of the other sheets with the formulas shown.

    The previous pricing system used a combination of timing, quantity, program type, and several other options to create primary keys that aligned with individual prices. As a purchase was created, the primary key would be built automatically in our master program sheet, then an Index/Match formula would look up and match the pricing primary key between the master program sheet and the pricing sheet. After that, several other variable fees would be calculated in the master program sheet using the referenced price, a weight overage fee, and a shipment overage fee.

    The new pricing system is based off of individual client annual commitments to program quantity, regardless of timing and other program types. The new pricing is based on the client name (which is standardized) and the current commitment level. The price is then calculated on the vendor status list (this is the sheet I share with you). The master program sheet now references the vendor status list for the 'Normal Sample Cost', then performs the calculation for the 'Total Sample Cost'.

    The issue is, the 'Total Sample Cost' for the previous method calculated correctly with the 'Normal Sample Cost'. With the new structure, 'Total Sample Cost' is not including the 'Normal Sample Cost'. No changes were made to the formula in the 'Total Sample Cost' column.


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Elliot Holder

    It looks like your INDEX formulas are working just fine, they're bringing in the correct cell data.

    However, I see that the values are appearing on the left of the cell instead of on the right. This indicates that they're stored as Text in the source sheet instead of stored as numerical, which means the SUM won't be able to add that number in (since it's text, not a number).

    Can you go to the new source sheet and check the data there? Is there a formula that's creating the $0.60?

  • @Genevieve P. Yes, and it looks like they are reading as text as well. I tried remove the dollar sign, but that did not change their state.


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Elliot Holder

    The reason the numbers are showing up as text is due to the quotes around them in that IF statement.

    For example, where you have "$0.50" you'll want to have 0.50

    Then once all the values are numbers in the formula without quotes, you can set the $ sign to the entire column using the formatting toolbar at the top of the sheet.

    Let me know if that makes sense or if you need help adjusting the formula!

    Cheers,

    Genevieve

  • @Genevieve P. Thanks a ton! Worked like a charm!

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem! I'm glad we could get to the bottom of it. 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!