Using vlookup with IFERROR and NOT ISBLANK

ss_happychappy

Hi guys and girls,

Scenario.

1. I would like to populate a cell on Sheet A from a vlookup on Sheet B. - This works.
2. I moved onto if the cell on Sheet B is empty DO not apply #NOMATCH to Sheet A and make a text comment e.g. No record. - This works with IFERROR

 =IFERROR(VLOOKUP([header1]@row, {Sheet B Range 1}, 4, false), "No Record")

What I would like to do now is wrap a if not blank statement around the above. The logic that a vlookup is not performed on a cell that already has a value.

When I apply this I receive #CIRCULAR REFERENCE. Can you please suggest the correct syntax.

=IF(NOT(ISBLANK([cell_in_SheetA_where_checking_if_not_blank]@row),IFERROR(VLOOKUP([header1]@row, {Sheet B Range 1}, 4, false), "")))

Thank you!

Paul Newcome

Instead of doing a cell reference, you will need to drop the VLOOKUP inside of the ISBLANK. Also double check you parenthesis. It looks like one may be misplaced at the end when it should be after the NOT/ISBLANK.

ss_happychappy

Hi @Paul Newcome

Thanks for getting back to me.

I have managed to get this working if the formula is added to another cell to look at the cell 'cell_in_SheetA_where_checking_if_not_blank'. This will then ignore the vlookup if that cell is populated.

The problem appears to occur when it is set to query on the same column.

On the forums people discuss helper columns, however, there appears to be manual copying afterwards?

I need a solution that checks the column, if it is populated ignore, otherwise vlookup and bring in the entry.

If I perform the following without a check then existing cells are overwritten.

=IFERROR(VLOOKUP([header1]@row, {Sheet B Range 1}, 4, false), "No Record")

Can I have an example of what would work here?

Thanks again.

Paul Newcome

Are you able to provide some screenshots for reference?

ss_happychappy

Hi Paul,

Screenshots below.

I did miss something out.

A form will be sent from Sheet B for someone to complete. This form will be completed and a row on a smartsheet populated.

Sheet A will have cells on a row that may require data from Sheet B where the form has been completed. When I say may I do not wish to have a cell overwritten where a value has been add prior to this change.

This is where vlookup come into play.

Vlookup to reference the cells on Sheet B, if there is something in that cell to pull it into a cell on Sheet A (if the cell is not already populated).

This works if I did not care about a cell being overwritten (which I do :) ) hence the if not isblank logic.

=IFERROR(VLOOKUP([header1]@row, {Sheet B Range 1}, 4, false), "No Record")

Screenshots below. I have intentionally made the column headers different if something needs to be done.

For this Sheet A should pull in data from Sheet B for example 2 (email) & example 3 (Number (of assets) & email) only. All other cells to remain unchanged.

example4 does not have anything to reference in Sheet B, all cells should therefore populate with 'No Record'

Hope this makes sense.

Thanks,

Sheet A

Sheet B

ss_happychappy

Hi All,

Any suggestions welcome :)

An alternative if not possible (sure it will be on this forum) is to hide for example Number (of assets) and create a new column called Assets. A formula is create to say if Number (of assets) has a value copy to Assets others vlookup Sheet B Assets column and pull that value in to Sheet A. If Sheet B Assets column is empty add No Record to cell.

Thanks,

Paul Newcome

Have you tried using the VLOOKUP in the NOT/ISBLANK yet?

=IF(NOT(ISBLANK(IFERROR(VLOOKUP([header1]@row, {Sheet B Range 1}, 4, false), ""))),IFERROR(VLOOKUP([header1]@row, {Sheet B Range 1}, 4, false), ""), "No Record")

ss_happychappy

Hi Paul,

Thanks for the feedback. That works to a point, but when I convert to column formula any existing values that were hard coded e.g. 10 (as we never had this process before) get overwritten with No Record.

Cells with e.g. 10 should retain their value.

Thanks again.

Paul Newcome

You cannot have manual entry and a formula in the same cell. It would have to be one or the other. For those that already have a value, you would need to add them along with the appropriate value to the reference sheet so that they are grabbed by the VLOOKUP.

ss_happychappy

Hi @Paul Newcome

Nice suggestion and tried this and working - thank you!

There is one issue remaining that I found this morning, could you / someone please help if the logic requires a change and provide me with something to try?

As previously noted, we are capturing information via forms. When a form is sent to multiple people with the same name within the cell of the primary column (example1 below) things break.

Smartsheet adds a column per forms response, however, vlookup appear to read top down i.e. it ignores the second entry and does not use both (Column 2 row 'Useful') is ignored.

Is there a way (not manually as we are automating everything) to ensure when vlookup references the cells that it checks the primary column? If the cell contains the same name e.g. example1 that all rows are pulled into the other sheet.

Thank you!