Using if function and vlookup

I would like to populate sheet the first sheet shown below which is entitled "Process Step Capture Tool" with data from the second sheet below entitled"Test Sheet" and I think I need to use the if and vlookup functions. What I am trying to do in the top sheet is say if the Process # is Process 1 then add the corresponding Process Name from the bottom sheet.

I hope that makes sense. Any help would be much appreciated.



  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @Colette Hughes,

    It is doable in a number of ways.

    You can use cell linkages (right click on the cell and "Link from Cell in Other sheet" and pick the relevant sheet/cell. However, this is very time consuming.

    Using formulae:

    For the Process Name in Process 1:

    =VLOOKUP([Column2]$1, {Test Sheet}, 2, false)

    Your cross sheet range should encompass all the columns required.

    You can then change the column and/or cell references as desired, along with the lookup column value.

    For example, the Process 2 Process Name would be:

    =VLOOKUP([Column3]$1, {Test Sheet}, 2, false)

    and the Process Purpose for Process 2 would be:

    =VLOOKUP([Column3]$1, {Test Sheet}, 3, false)

    However, this is somewhat manual in terms of entry so here is another alternative.

    First, add a helper column with a column formula (this can be hidden for display purposes):

    =IF([Primary Column]@row = "Process Name", 2, IF([Primary Column]@row = "Process Purpose", 3, IF([Primary Column]@row = "Accountable Lead", 4)))

    You can keep expanding this nested IF statement if you have more columns of data.

    Indent your rows Under Process # (i.e. Process Name - Accountable Lead").

    You can then use the following formula:

    =IFERROR(VLOOKUP(PARENT(), {Test Sheet}, [Helper column]@row, false), "")

    This is draggable across the whole area required (so you'll only need to enter it once for a given section) which speeds things up a bit rather than needing to copy/paste/update a formula. The IFERROR section just means if you've dragged it a bit too far you won't have unsightly errors show up.

    There is also an issue that contacts won't show up as contacts as the columns can only support a single type.

    Sample showing both formula methods output:

    Hope this helps, but if you've any problems/questions then just post! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!