# Trying to multiply 2 cells- one contains a long IF & VLOOKUP statement

Options
✭✭✭✭✭

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

Tags:

• ✭✭✭✭✭
Options

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 Covid-19 crisis.

Have a great day and stay safe.

Cindy

«1

• ✭✭✭✭✭✭
Options

Try wrapping the cell reference in your multiplication formula in a VALUE function.

=[First Cell]@row * VALUE([VLOOKUP cell]@row)

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Is the formula in your original post what is in the [Scissor Rental Rate] column?

• ✭✭✭✭✭
Options

=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

• ✭✭✭✭✭✭
Options

Ok. What is in this range?

{Scissor Rates 2}

Is there a formula there or manual entry?

• ✭✭✭✭✭
Options

(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

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭
Options

this is the screen shot once I added another column

• ✭✭✭✭✭✭
Options

It looks like the data you are pulling from Excel is coming in as text. Let's try this...

=VALUE(SUBSTITUTE([Pricing Column]@row, "\$", ""))

• ✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!