Issues with Vlookup formula

J M

I was running into some issues with running a live copy column on a new sheet I made, that would continually updates itself with data from a reference master sheet's column.

I started off trying to use the cell link feature; however, it appears that this wouldn't add on any new rows that were added to the master sheet after the cell link was originally initiated.

I have now tried using vlookup formulas to pull this data into the necessary rows on this column but I keep getting a "#unparseable" message when i try to run my formula.

I have included a copy of the reference sheet I am trying to use below and the formula I have been using:

=VLOOKUP([Project Phase]@row, {Small Project Tracking Range Phase}, 1, false)

Does anyone happen to know why this would be returning "#unparseable" error message?

Additionally does anyone know if this is the best method for creating a column that copies a live version of another column from a different sheet and that also automatically adds new rows to that column that are added onto the master sheet? Happy to look into alternative methods if there is a simpler way to accomplish this.

Thanks in advance for any assistance that can be provided!

Genevieve P.

Hi @J M

To expand on @Paul Newcome's correct answer above, I believe you are receiving an error because the first cell you're referencing [in these] with @row is trying to reference a cell in the other sheet.

The reference [in these] needs to be the value to match in the same sheet as the formula.

So in the first screen capture on your second post, you have the following column names:

Task Name / Phase ... etc

If you're matching the Phase, as in your first formula example, then you need to reference the Phase column in this current sheet:

=VLOOKUP(Phase@row, {Small Project Tracking Range 1}, 2, false)

This formula will look for the value in your current Phase column, find it in the first column range of {Small Project Tracking Range 1}, and bring back the value in the second column (Functionality).

Does that make more sense for how a VLOOKUP works? It requires a value to look up in the other sheet.

Let me know if we can help further!

Cheers,

Genevieve

Paul Newcome

The range for the VLOOKUP needs to be at a minimum two columns wide. The leftmost column being the column to match on and then you will need to make sure the column to pull from is inside of that same range. You would then enter the column number for the VLOOKUP to pull from using the leftmost column as 1.

You are also going to want to make sure that [Project Phase] is the exact name of the column in the sheet you have the VLOOKUP in that you are matching on.

J M

Paul,

Thank you for the assistance!

I attempted to make adjustments to the reference ranges and formulas per the above direction. I tried this in two way, one with "Project Phase as the "1" column and calling for it and another with Project Phase being the second column and calling it with "2". Unfortunately is still returning an "#unparseable" message in both cases. It also appears that the "Project Phase" string used matches the original column.

Am I potentially running into a syntax issues with the @row portion?

=VLOOKUP([Project Phase]@row, {Small Project Tracking Range 1}, 1, false)

=VLOOKUP([11/18 COVID STATUS]@row, {Small Project Tracking Range Full}, 2, false)

Paul Newcome

Your formula should look something like this...

=VLOOKUP([column to match on]@row, {Source Sheet Range}, #, false)

{Source Sheet Range} should have the column that is being matched against on the far left of the range.

# should be the column number of the column you want to pull data from with the match column being 1.

So if your source sheet is set up as

[Task Name].....[Project Phase]

Then the range would cover both columns and the # would be 2. You would be matching on the task name and pulling from the second column in the range.

Genevieve P.

Hi @J M

To expand on @Paul Newcome's correct answer above, I believe you are receiving an error because the first cell you're referencing [in these] with @row is trying to reference a cell in the other sheet.

The reference [in these] needs to be the value to match in the same sheet as the formula.

So in the first screen capture on your second post, you have the following column names:

Task Name / Phase ... etc

If you're matching the Phase, as in your first formula example, then you need to reference the Phase column in this current sheet:

=VLOOKUP(Phase@row, {Small Project Tracking Range 1}, 2, false)

This formula will look for the value in your current Phase column, find it in the first column range of {Small Project Tracking Range 1}, and bring back the value in the second column (Functionality).

Does that make more sense for how a VLOOKUP works? It requires a value to look up in the other sheet.

Let me know if we can help further!

Cheers,

Genevieve

Paul Newcome

@Genevieve P Good catch. I didn't even realize the value to match on was being referenced from the other sheet like that.

J M

Genevieve and Paul,

Thank you for the assistance! Looks like it is up and running.