Calculating the overall value of the cell based on the dictionary in google sheets
I have a value in one cell looking something like this: (The product name and the amount required)
[Product1] 1, [Product2] 2, [Product3] 3
As well i have a dictionary looking something like: (Each product value)
| A | B |
|---|---|
| Product1 | 100 |
| Product2 | 125 |
| Product3 | 225 |
Im curious to know if its possible to get the overall value of the cell's products? So in that case i need to get a value of 1025
Honestly didnt tried much, becuase im not that familiar with google sheets. Don't even know how to approach the problem.
Solution
You may try:
=let(Σ,index(substitute(split(tocol(split(A2,", ",)),"] ",),"[",)),
sumproduct(ifna(vlookup(index(Σ,,1),E:F,2,)),index(Σ,,2)))
- Is there a Google Sheets formula to put the name of the sheet into a cell?
- Google Script Trigger no longer activating properly
- Create a 2 column table combining data from 4 columns
- Append results of a filter within results of another filter
- Why I can't write in a google sheet cell with Apps script?
- How to calculate value based on tax bracket using Google sheet formula?
- Reference a sheet with today's date as the name
- Filter Mismatch on Named Function When Adding Variable
- Make fixed map in modified Minesweeper clone
- Required permissions: https://www.googleapis.com/auth/spreadsheets
- Google Sheets array formula not working across multiple columns
- How can I scrape from tiprank through Google Sheet Apps Script?
- Get Father and Children in an API with APPS SCRIPT (GSHEETS)
- Use ArrayFormula to propagate row with specific formula
- On Google Sheets (and only built-in functions allowed, no Google Apps Script) Is it possible to simulate pipe function?
- How to set a Google Sheets' Cell Note based off a Cell Value that begins with a Plus Sign
- Using a multi-select cell to create a total Sum in Google Sheets formula
- Regex AND operator in Google Sheets
- Google Sheets Conditional formatting custom formula: if current cell is empty, but other cell is not
- Tabletop.js can't see Google Spreadsheet data
- How do you find and replace characters with app scripts?
- Harvest data from Google Spreadsheet?
- SUMIFS with OR criteria in Google Sheets
- Parse error from using .setValue or .SetFormula to set a formula containing a query function that has multiple single and double quotes
- Formula parse error after “setformula” in google sheets
- Multiple COUNTIFS inside IFS formula (FORMULA PARSE ERROR)
- Google Application Script QUERY "Formula Parse Error"
- setFormula statement producing Parse Error
- setFormula parenthesis or quotes missing?
- Getting parsing error setFormula Google Script