Excel VLOOKUP stop working properly

Question

Excel VLOOKUP stop working properly

Andrew Littlefield 20

In one workbook I had the formula in table 1 "VLOOKUP(B2,Sheet2!A:C,3)".

When I originally created the formula it worked fine, but now it returns a value that is incorrect. The returned is in Sheet2!A:C,3 but it does not align with the reference value in B2.

I have tried to adjust the formula various ways and even changed to a IF statement, but I either get the incorrect value or a SPILL or REF error. I have also attempted to rebuild the worksheet from scratch with the same results.

My other VLOOKUPs in other workbooks still work as designed.

Barry Schwarz 4,871 Reputation points

2023-10-10T17:25:50.1+00:00

Are the values in column A sorted properly? What type of IF statement did you try?

Show us the value in B2,, the values in columns A&C above and below the returned value, and the values in columns A&C above and below the value in B2.

Answer accepted by question author

1 additional answer

Your answer

Barry Schwarz 4,871 Reputation points

2023-10-10T17:25:50.1+00:00

Are the values in column A sorted properly? What type of IF statement did you try?

Show us the value in B2,, the values in columns A&C above and below the returned value, and the values in columns A&C above and below the value in B2.

Answer 1

Hi,

I created a sample about your tables, I suggest you change the lookup_value from [Full Name] to B2:

=VLOOKUP(B2,Sheet2!A:C,3,FALSE)

User's image

I also suggest you refer to this support article and check the error in table about the error #SPILL!:

https://support.microsoft.com/en-us/office/how-to-correct-a-spill-error-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Andrew Littlefield 20

the formula is in Column M

User's image

Viorel 125.7K Reputation points

2023-10-10T18:24:22.2+00:00

Try to use this formula for M2: =VLOOKUP(B2,Clientele!A:C,3).

Or try this formula: =VLOOKUP([@[Full Name]],Clientele!A:C,3).
Andrew Littlefield 20 Reputation points

2023-10-10T18:36:06.49+00:00

that only worked for the copy of the spreadsheet that I redacted the names from.

When I tried to input the formula in my live spreadsheet it will not return the right "CM" per each "client".
Barry Schwarz 4,871 Reputation points

2023-10-11T02:53:08.5233333+00:00

Since the formula works with the redacted data but not the live data, the problem seems to be related to the data and not the formula. We don't see the live data so we cannot help with your analysis.

Note that since you did not specify the fourth argument to VLOOKUP, the default is TRUE which means find approximate matches. I have no idea how Excel scores approximations. Does adding FALSE to the formula alter the outcome?

There is an approach that may help you do the analysis: Replace the A:C portion of the formula with $A1:$C5. Test with appropriate values in B2. If that works, expand to $C$10. Test again. Repeat the expansion and test until you hit the problem. Something in the last expanded data could be the cause.

Share via

Excel VLOOKUP stop working properly

1 additional answer

Your answer