Make multiple words equal a value.
Can you make more than one word equal to a value in Excel? I know this formula =VLOOKUP(C1,A$1:B$20,2,FALSE) will work for one word, but not multiple words. I need it for multiple words. I need the cell to use multiple words in other cells to equal out one price. Example: Lettuce - $0.05, Tomato - $.06, Onion - 0.04..etc and for the cell to add up all the words and the prices associated with them. Thank you.
Sounds like you need a sumif stmt? More detail needed to write one though.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
so this would be an example, say that your values like Lettuce, onion, tomato etc are in col A and your prices in col B. You can put this formula elsewhere. =SUMIF(A:A,"lettuce",B:B) Or create a table of the values, say in col C you make a defined list of your products like Lettuce in C1 for example, then change the formula to this =SUMIF(A:A,C1,B:B) etc.
In Column A I have all the items in this case they are food ingredients such as lettuce, tomato, onion, pickle, etc. In Column B I have all the items prices. In column C1 I have written the word Pickle which it values at $0.05. In D3 I have the formula =VLOOKUP(C1,A$1:B$20,2,FALSE . So it recognizes the word Pickle and associates it with $0.05 since that is that items price. The problem I'm having is that I can only use one word. If I add "lettuce" to the same cell as the word "Pickle" it not longer recognizes the value. I'm just looking for the cell to allow me to use more than just one food ingredient to get my total value. My goal is to put all "lettuce, tomato, pickle, onion" all into one cell and it instantly tell me the total price of all those ingredients based just off the words since they their prices already. I'm bad at explaining this I'm sorry.
image.jpg
This is my quick example
I can't think of any way to do that without using VBA or a very complicated find or search function. If you wanted to keep them in separate cells it should be pretty easy, you could use multiple sumif stmts or use a sumifs stmt.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
You can use SEARCH to find then SUMPRODUCT like this:
Untitled.png
Assuming A1:A8: Names; B1:B8: prices; E1: Name consolidation
=SUMPRODUCT(ISNUMBER(SEARCH(A1:A8,E1))*B1:B8)
Quang PT
Hello bebo021999,
This works well for your example but XxshawowdawgxX has both "Bacon" and "Bacon bits" in his ingredient list. When one ingredient is a substring of another you may get the wrong result, e.g. if E1 contains "Bacon bits" then your formula will add the prices for both Bacon and Bacon bits.
To prevent that, assuming all ingredients separated by commas you can use this amended formula:
=SUMPRODUCT(ISNUMBER(SEARCH(","&A1:A8&",",","&E1&","))*B1:B8)
Audere est facere
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks