Trying to multiply 2 cells one contains a long IF & VLOOKUP statement
I have the following problem. I am trying to multiply the value of two cells. One is a numeric value entered into a form the other is the answer to a nested IF ( VLOOKUP) formula. I keep getting and #INVALID VALUE error.
I need the result to be a $ value that comes from the length of rental period selected multiplied by the $ value returned from the following formula 
=IF([Rental Period]1 = "Daily", VLOOKUP([Boom Lifts]1, {Boom Rates}, 2, false), IF([Rental Period]1 = "Weekly", VLOOKUP([Boom Lifts]1, {Boom Rates}, 3, false), IF([Rental Period]1 = "Monthly", VLOOKUP([Boom Lifts]1, {Boom Rates}, 4, false), false)))
The above formula will return a $ value pulled from another sheet based on answers in the form submitted.
Can anyone help?
Thanks,
Cindy
Best Answers

Not necessarily. The extra column on the reference sheet was just to test the data. Now that we know what is going on, you can actually delete the extra column on the reference sheet and just modify the VLOOKUP.
VALUE(SUBSTITUTE(VLOOKUP(......................), "$", ""))
Do that with each of the VLOOKUP functions in your IF statement, and you should be good to go.
=IF([Rental Period]1 = "Daily", VALUE(SUBSTITUTE(VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 2, false), "$", "")), IF([Rental Period]1 = "Weekly", VALUE(SUBSTITUTE(VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 3, false), "$", "")), IF([Rental Period]1 = "Monthly", VALUE(SUBSTITUTE(VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 4, false), "$", "")), "")))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

It has worked thanks so much for all of your help. I would like to get some more involved training on formulas but that might have to wait until after this Covid19 crisis.
Have a great day and stay safe.
Cindy
Answers

Try wrapping the cell reference in your multiplication formula in a VALUE function.
=[First Cell]@row * VALUE([VLOOKUP cell]@row)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

=[Length of Rental]1@row*VALUE([Scissor Rental Rate]1@row)
the above is how I have written the formula now. I now have an #UNPARSEABLE error.
not sure where to go from here.
Thanks
Cindy

Remove either the row number or @row. You should only use one or the other. The @row reference simply tells the formula to evaluate the column on whatever row the formula is on. It is a little more efficient and helps avoid incorrect row numbers.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

=[Length of Rental]@row * VALUE([Scissor Rental Rate]@row)
This is how I have rewritten the formula, I am now getting an #INVALID VALUE error.
Thanks Cindy

Is the formula in your original post what is in the [Scissor Rental Rate] column?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

=IF([Rental Period]1 = "Daily", VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 2, false), IF([Rental Period]1 = "Weekly", VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 3, false), IF([Rental Period]1 = "Monthly", VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 4, false), "")))
It is this VLOOKUP formula.
Thanks
Cindy

Ok. What is in this range?
{Scissor Rates 2}
Is there a formula there or manual entry?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

(Scissor Rates 2) comes from my reference sheet, the user manually enters the model of scissor in the form and the rate is populated to the sheet from the formula that is above.
Cindy

I understand that you are using the formula above to pull the rate from the source sheet. What I am asking is who the data on the source sheet itself is populated. If the user is entering a model of scissor, where are the numbers coming from on the source sheet that you are pulling?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Sorry,
the user, who is filling in the form, selects the model of scissor from a drop down list. Whatever their response is is then used to find the appropriate pricing. This pricing is from a excel sheet that I imported into a smartsheet to use as a reference.

Ok. So it is possible that what you are importing from Excel is being imported as text values instead of numeric.
Let's go to the reference sheet...
Try creating a separate text/number column and entering a formula that will convert your current pricing column into a numeric value.
=VALUE([Pricing Column]@row)
What happens? Are you able to provide a screenshot of the pricing column?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

this is the screen shot once I added another column

It looks like the data you are pulling from Excel is coming in as text. Let's try this...
=VALUE(SUBSTITUTE([Pricing Column]@row, "$", ""))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

okay, so I have entered that formula and I seem to have 3 new columns that give me new pricing values. Do I need to have them in a $ formula.
I am assuming I now need to change my VLOOKup to look for these rows in order to get the proper numeric value so that I can get it to multiply properly.
Thanks
Cindy

Not necessarily. The extra column on the reference sheet was just to test the data. Now that we know what is going on, you can actually delete the extra column on the reference sheet and just modify the VLOOKUP.
VALUE(SUBSTITUTE(VLOOKUP(......................), "$", ""))
Do that with each of the VLOOKUP functions in your IF statement, and you should be good to go.
=IF([Rental Period]1 = "Daily", VALUE(SUBSTITUTE(VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 2, false), "$", "")), IF([Rental Period]1 = "Weekly", VALUE(SUBSTITUTE(VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 3, false), "$", "")), IF([Rental Period]1 = "Monthly", VALUE(SUBSTITUTE(VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 4, false), "$", "")), "")))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 379 Global Discussions
 210 Industry Talk
 441 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 300 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!