Using if function and vlookup

Colette Hughes

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.

Thanks...Colette

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! 🙂