How to ignore blank cells with VLOOKUP formula

Mallory N

I have been searching for hours with no luck on fixing this issue. I have a grid that I am using to populate raw data. I would like to combine this data into a more clean grid that I will be able to pull reports from. However, there are blank cells in the raw data grid so it is not pulling the information from where there is data filled in. How can I tell Smartsheet to ignore the blank cells?

This is the formula that I am using:

=VLOOKUP([Quote Number]@row, {Work Breakdown Structure Raw Data - All Cells}, 4, false))

If there is data in the first instance of the column, the formula works fine (see highlighted cell below)

But if there are any blanks before the cell with a value, it comes back #UNPARSEABLE.

Any help is greatly appreciated.

Paul Newcome

Try an INDEX/COLLECT. You will need a slight tweak for each of the different department columns.

=IFERROR(INDEX(COLLECT({Form Sheet Engineering Data Entry Column}, {Form Sheet Quote Number Column}, @cell = [Quote #]@row, {Form Sheet Department Column}, @cell = "Engineering"), 1), "")

Then for the PMO department, you would change the first range to pull from the PMO dept.'s data entry column and the final criteria to "PMO" instead of "Engineering". So on and so forth until you have taken care of each department.

MichaelTCA

Hello @Mallory N

First, move away from the VLOOKUP function. :)

Second, you can use the INDEX/COLLECT function to set multiple criteria. If you want to specify an array as the range and manually enter the column number to reference, then you still can, but I recommend only selecting a single column as a range being evaluated.

For example:

=INDEX(COLLECT({Work Breakdown Structure Raw Data - All Cells},{COLUMN WITH THE QUOTE NUMBER},[Quote Number]@row,{COLUMN WITH THE BLANK VALUES},[Column with blank values]@row<>""),1)

The 1 at the end is the column number.

bisaacs

To clarify, do you mean if there's a blank column to the left of the search value column it returns #UNPARSABLE?

Have you tried using an INDEX(MATCH) formula instead?

If you want to stick with the VLOOKUP function, maybe wrap VLOOKUP in an IF(ISBLANK() like so:

=IF(ISBLANK([Quote Number]@row), "", VLOOKUP([Quote Number]@row, {Work Breakdown Structure Raw Data - All Cells}, 4, false))

It also looks like you might have an extra parenthesis at the end of the VLOOKUP formula you provided

Mallory N

I am using a form to populate only some cells associated with each quote (the primary column). My desire is for the 5 rows that will be associated with the same quote number from the different submission of the form to be copied over to another easy to read grid.

So in this example that I have shown, quote number is "test" - in the other grid, i would like for the values entered by both Engineering and PMO to populate on the same line. Because the Engineering line is first, it only populates the blank cell shown for that instance. If I add a value into the highlighted cell, it will show up onto the other sheet, but that obviously takes away from the point of this process being automated.

I have not been successful in trying to use INDEX/COLLECT or the ISBLANK functions. I am using SmartsheetGov if that matters.

MichaelTCA

The form and your subscription should not make a difference, in your situation.

You brought automations into the mix.

Could you provide me the whole situation?

What automations are you using? Copy row? Move Row?

What's the trigger? Are there any conditions?

Are you trying to use a lookup function as a trigger?

Mallory N

I have two sheets - one with raw data that is populated from a form and another that I would like to pull information from the raw data to combine all raw data for each quote into one line. There are no automations involved in this portion of the workbook.

The form is set up that depending on the department, fields will populate to only fill certain columns that are associated with that department. I am trying to use VLOOKUP to transcribe data from the raw data sheet to the combined data sheet to put all of the various data from each department into the same line, but this has not been working because it is pulling the blank cells from the raw data sheet. I am looking for something to include in the function to make the system ignore the blank cells.

This is the raw data sheet:

This is the combined data sheet:

Paul Newcome

Try an INDEX/COLLECT. You will need a slight tweak for each of the different department columns.

=IFERROR(INDEX(COLLECT({Form Sheet Engineering Data Entry Column}, {Form Sheet Quote Number Column}, @cell = [Quote #]@row, {Form Sheet Department Column}, @cell = "Engineering"), 1), "")

Then for the PMO department, you would change the first range to pull from the PMO dept.'s data entry column and the final criteria to "PMO" instead of "Engineering". So on and so forth until you have taken care of each department.

Mallory N

Thank you Paul, that worked beautifully for the initial row. However, it is not allowing me to convert to a column formula. Is there something special I should be doing to make this work?

Paul Newcome

The formula I provided should work as a column formula. Are you able to provide a screenshot of the formula open in the sheet as if you are about to edit it?

Mallory N

Paul Newcome

That's very odd. Are you using this column in any dependency settings or anything?

Mallory N

No, I am not. It allowed me to convert to a column formula on the second column after the primary column (where the name is seen), but not any of the other columns. They all show greyed out like shown above.

Paul Newcome

Are any of them part of any automations?

Mallory N

No, there aren't any automations on the sheet yet.

Paul Newcome

What happens if you just click on the cell containing the formula one time? Don't actually open it as if you are editing it. Just select the cell itself?

Mallory N