# VLOOKUP returning incorrect results

Copper Contributor

# VLOOKUP returning incorrect results

I have a large complex workbook that I've been using for years with great success. But all of a sudden I'm getting incorrect values.
Here's the situation.

One tab in the workbook (let's call it ACCOUNT) has dates in column A and amounts in column C.

There's a range ACCT_PMT that's defined over the 1st 3 columns and all rows in ACCOUNT.

A different tab (call it PAYMENT) also has dates in column A. In its column C, I have the formula
=VLOOKUP(Annn,ACCT_PMT,3) - where nnn is the current row number.

For instance, in cell C35 it would be =VLOOKUP(A35,ACCT_PMT,3)

Suddenly within the last few days the cells in PAYMENT column C are NOT returning the appropriate values, and it's not obvious where they're getting the values they display.

Example: ACCOUNT cell A18 has 09/15/22, and cell C18 has 517.26

PAYMENT cell A35 has 09/15/22, so I would expect the formula in C35 to return 517.26

But it doesn't. and I nowhere in the ACCOUNT tab do I see the value that's displayed in PAYMENT's C35.

As I say, I've been using this workbook for years, and have never encountered anything like this until the last few days.

Any idea what's going on?

3 Replies

# Re: VLOOKUP returning incorrect results

Does it help if you add FALSE as 4th argument, i.e. in C2

=VLOOKUP(A2,ACCT_PMT,3,FALSE)

and fill down?

# Re: VLOOKUP returning incorrect results

@HansVogelaar Yes, that worked! Thanks! But I don't understand why. Especially since it's been working correctly for years. Have I just been lucky?

# Re: VLOOKUP returning incorrect results

@Attila_the_Pun

FALSE specifies that you're looking for an exact match.

Without it, VLOOKUP will look for an approximate match, but this will only work if the first column of the lookup range is sorted in ascending order, otherwise the results are unpredictable.