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), "$", "")), "")))

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)

=[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.

=[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?

=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?

(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?

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?

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, "$", ""))

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), "$", "")), "")))
Help Article Resources
Categories
Check out the Formula Handbook template!