VLOOKUP issue

S0ccer7

I am trying to pull totals from my 2023 sheet into my 2024 sheet. The totals I am pulling are in a different column but same row as where I want them, so I decided to use VLOOKUP.

2023 Sheet:

2024 Sheet:

I want 'Total 2023' to be pulled and placed in 'Previous Years Total' for each corresponding row.

My formula:

=IF(VLOOKUP("Installed 2in PL - " + BCA@row, {2023 - Install/Gas Range 4}, 6, false) = 0, 0, "Installed 2in PL - " + BCA@row, {2023 - Install/Gas Range 4}, 6, false)

References:

• BCA@row is each projects unique identifier (in this case it will pull 309627) so that I can use the same formula for multiple projects. I have a BCA column on both sheets.
• {2023 - Install/Gas Range 4} is columns from 'Project' to 'Total 2023'

KPH

Hi @S0ccer7

I think I understand what you want to do but rather than jumping straight to a formula I have been trying to work out how you got to where you did so I can explain the changes needed and, as what you're doing seems more complex than needed, check that I am not missing a requirement. I do this by taking your formula and breaking it down into smaller pieces to (1) test what works and what does not, and (2) understand your thought process. I'm going to share my process with you.

This is the first function in your formula (simplified slightly) and it works fine

=VLOOKUP(Project@row, {2023 - Install/Gas Range 4}, 6, false)

This looks at the value in the Project column, and matches that against the 1st column in your 2023 sheet (which I have called {2023 - Install/Gas Range 4}). When there is a match, it returns the value in column 6. TBH this looks like all you need to achieve your aim. However, there is a lot more to your formula, so I continue.

I switched out Project@row for the reference you had. This also works.

=VLOOKUP("Installed 2in PL - " + BCA@row, {2023 - Install/Gas Range 4}, 6, false)

However, I don't know why you are entering the secondary column as a value in the formula and not either entering it as [Secondary Column]@row or using Project@row without the concatenation. But it technically works. If you do it this way you will need to edit Installed 2in PL - in each row. Using this formula below (or the one I tested first) might be better for you as it will work for all rows (if that is indeed what you want).

=VLOOKUP([Secondary Column]@row + BCA@row, {2023 - Install/Gas Range 4}, 6, false)

I'm unsure what you are trying to do with the IF. It looks like, if the result of the above function is 0 you want to return 0 and if not do something else.

I checked that part like this, using the text "Something else" rather than another function:

=IF(VLOOKUP("Installed 2in PL - " + BCA@row, {2023 - Install/Gas Range 4}, 6, false) = 0, 0, "something else")

It also works.

If the value pulled from 2023 is 0 it puts 0, if it is anything else it writes "something else".

Then we get to this part:

 "Installed 2in PL - " + BCA@row, {2023 - Install/Gas Range 4}, 6, false)

The part in bold is going to be a problem. Without that, if the value returned from the 2023 table is not 0 it would return "Installed 2in PL - " + BCA@row (in the same way I returned "something else").

But then the formula hits the comma which it is not expecting and it will fail. It looks like you might be trying to repeat the VLOOKUP, so that if the result isn't 0 it outputs the actual number. This is unnecessary. The VLOOKUP without the IF will output the actual number. Which brings me back to thinking either of these might just work:

=VLOOKUP(Project@row, {2023 - Install/Gas Range 4}, 6, false)

=VLOOKUP([Secondary Column]@row + BCA@row, {2023 - Install/Gas Range 4}, 6, false)