I need help with a formula, I need it to return a blank cell.

Cindy Bowker

I have the following 2 formulas:

=IF([Rental Period]3 = "Daily", VLOOKUP([Scissor Lifts]3, {Scissor Rates 2}, 2, false), IF([Rental Period]3 = "Weekly", VLOOKUP([Scissor Lifts]3, {Scissor Rates 2}, 3, false), IF([Rental Period]3 = "Monthly", VLOOKUP([Scissor Lifts]3, {Scissor Rates 2}, 4, false), "")))

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

I would like it to return a blank cell if there is not an exact match. Basically if whoever submits the form doesn't request a scissor lift the cell on Smartsheet will be blank. Currently it is telling me #NO MATCH. If the boom or scissor is selected then the formula does work as it should.

I have a feeling this is a simple fix I am just not seeing it. If anyone can help I would appreciate it.

Cindy

Paul Newcome

You would want something more like this...

Start typing:

=IFERROR(

enter your original formula (without the starting = sign)

comma

double quotes

close parenthesis

=IFERROR(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))), "")

Cindy Bowker

Thanks you so much it worked. I appreciate the help. I knew I was just missing something.

Have a great day

Cindy Bowker

Can you explain how to use the INDEX/MATCH function as well. I know I can move the columns around but it might be helpful going forward to learn how to use the INDEX/MATCH.

I appreciate all the help you have given me so far. I figured I had just set something up wrong.

Cindy

Cindy Bowker

I got it working by switching the columns around. If you could give me a brief rundown of the INDEX/MATCH that would be awesome and I could use it in the future.

Cindy Bowker

thank you so much Paul, I will keep this for reference as I still have some more work to do on the sheet.

You have been an immense help with all of this. I hope you don't mind if I keep this for reference and it I contact you in the future if I have other questions.

Have a great day and stay safe.

Cindy Bowker

Paul Newcome

Happy to help! 👍️

Paul Newcome

An IFERROR function should be the fix.

=IFERROR(original_formula, "")

Cindy Bowker

=IFERROR(IF([Rental Period]3 = "Daily", VLOOKUP([Scissor Lifts]3, {Scissor Rates 2}, 2, false), IF([Rental Period]3 = "Weekly", VLOOKUP([Scissor Lifts]3, {Scissor Rates 2}, 3, false), IF([Rental Period]3 = "Monthly", VLOOKUP([Scissor Lifts]3, {Scissor Rates 2}, 4, false), ""))))

I have redone the formula to read as above but know I get a #INCORRECT ARGUMENT SET ERROR.

Not sure if I have added the IFERRor correctly.

Thanks

Paul Newcome

You would want something more like this...

Start typing:

=IFERROR(

enter your original formula (without the starting = sign)

comma

double quotes

close parenthesis

=IFERROR(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))), "")

Cindy Bowker

Thanks you so much it worked. I appreciate the help. I knew I was just missing something.

Have a great day

Paul Newcome

Happy to help! 👍️

Cindy Bowker

Hi Paul, I have somehow managed to mess up a very simple VLOOKUP formula, it was working but then I made a change to the sheet and it suddenly quite working again.

This is the formula; =VLOOKUP(Project1, {project reference Range 1}, 1, false), it is looking for a project number from a project name that is entered from a form.

this is a print screen of the reference sheet

not sure what I am missing, can you help.

Thanks Cindy

Paul Newcome

Let's take a look at the 3rd value in a VLOOKUP function...

=VLOOKUP(Project1, {project reference Range 1}, 1, false)

In this instance it is the number 1. This value tells the function which column to pull from. Entering the number 1 is telling it to pull from the leftmost column which is the column that the lookup value should be in.

SO changing the 1 to either a 2 or a 3 depending on which column you want to pull from should do the trick.

If it does not, let me know. There are a few other things we can take a look at.

Once we get it solved (and if you are interested), I would be happy to walk you through an INDEX/MATCH which operates the same way as a VLOOKUP but is MUCH more flexible.

Cindy Bowker

I have tried changing that value and still get the same result. The project name is entered from a drop down list on a form submittal. This value is used to find the Project number which is located in column 1 of the reference sheet and the project name is in column 2 of the reference sheet.

thanks

cindy

Paul Newcome

Ok. So the column you are referencing for the value to search for must be on the far left in a VLOOKUP. So if you are trying to pull a project number based on the project name, then the project name column would have to be on the far left of the column set you are referencing. If you would like to maintain your current column placement where the lookup value is NOT in the far left column, we would need to replace the VLOOKUP with an INDEX/MATCH.

Cindy Bowker

I would love to learn about the INDEX/MATCH function as well as some of the formulas I am having to use are getting complicated

Cindy Bowker

Can you explain how to use the INDEX/MATCH function as well. I know I can move the columns around but it might be helpful going forward to learn how to use the INDEX/MATCH.

I appreciate all the help you have given me so far. I figured I had just set something up wrong.

Cindy

Cindy Bowker

I got it working by switching the columns around. If you could give me a brief rundown of the INDEX/MATCH that would be awesome and I could use it in the future.

Paul Newcome

Ok. So INDEX/MATCH as I said before is basically a much more flexible version of a VLOOKUP. INDEX/MATCH only references single columns at a time, so it doesn't matter which order they are in, and you can move the reference columns around without having to worry about breaking anything.

=INDEX(range_to_pull_from, row_number, [optional_column_number])

Basically we designate which range you want to pull the data from. Then we designate which row to pull it from. Using INDEX/MATCH like we are about to only referencing single columns, we can just leave out the column number portion.

But how do we automatically tell it which row number to pull from? That's where the MATCH function comes in. The MATCH function generates a number based on where within a range your specified data was found. If you are only searching a single column for your specified data, then this will essentially replicate the row number.

=MATCH(search_criteria, range_to_search, match_type)

I prefer to leave the last section of match_type as 0 (zero) because that tells it to look for an EXACT match. This provides the most accurate results.

So if we want to pull the Project Number based on the Project Name, we would start our INDEX by specifying the Project Number column on the other sheet (range to pull from).

=INDEX({Other Sheet Project Number Column},

Now we use the MATCH to generate the row number for the INDEX function.

MATCH([Project Name]@row, {Other Sheet Project Name Column}, 0)

Then we drop that into the row_number portion of the INDEX function and close it off.

=INDEX({Other Sheet Project Number Column}, MATCH([Project Name]@row, {Other Sheet Project Name Column}, 0))

And there you have it... An INDEX/MATCH that will pull the Project Number based on the Project Name. It functions like a VLOOKUP but provides additional flexibility.

Cindy Bowker

thank you so much Paul, I will keep this for reference as I still have some more work to do on the sheet.

You have been an immense help with all of this. I hope you don't mind if I keep this for reference and it I contact you in the future if I have other questions.

Have a great day and stay safe.

Cindy Bowker

Paul Newcome

Happy to help! 👍️