Index and Match Used in Branching Formulas
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.
Best Answer
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | 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.
-
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?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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.
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. Thanks a ton! Worked like a charm!
-
No problem! I'm glad we could get to the bottom of it. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!