Index and Match Used in Branching Formulas

Elliot Holder

Screenshot 2022-01-07 155018.png

Uploaded Jan 7, 202266.5 KB

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.

Genevieve P.

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

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, andree@workbold.com)

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!

Elliot Holder

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.

StandardTotalCostFormula.png

Uploaded Jan 10, 202280.75 KB

NewIndexMatchPricingFormula.png

Uploaded Jan 10, 202269.72 KB

PreviousIndexMatchPricingFormula.png

Uploaded Jan 10, 2022108.07 KB

PreviousPricingGrid.png

Uploaded Jan 10, 202291.75 KB

Genevieve P.

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?

Elliot Holder

@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.

NewPriceCalculation.png

Uploaded Jan 14, 202223.77 KB

Genevieve P.

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

Elliot Holder

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

Genevieve P.

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