Dumbfounded by VLOOKUP error

[zhengming]

Hi,

I have a VLOOKUP function of the following format VLOOKUP(A1, B1:C20, 2, FALSE).

A1 can take any date value
Range B1:C20 contains date values from March 1 to March 20 (column B) and a corresponding sales number (column C).

However, if I in cell A1 type in 3/31/2014 it returns the value for March 20 instead of N/A. In fact, for any number > March 20, it will return the March 20 number.

I don't understand why this is happening, since I've opted for "FALSE" in argument 4 - shouldn't that imply that only EXACT matches are returned?

Help extremely appreciated!!

/Nic

[Aladin Akyurek]

In D1 enter and copy down:

=ISNUMBER(B1)

All of the cells of the D range should show TRUE. Any FALSE value would indicate a text (date).

[MarvinP]

Hi zhengming and welcome to the forum,

Perhaps this will fix the problem?

VLOOKUP(A1, $B$1:$C$20, 2, FALSE)

Making the lookup range fixed might be the cure.

[zhengming]

Hmm. Didn't think about this. In reality, B1:B20 is B1:B40 with every other cell a text value. I presume VLOOKUP doesn't appreciate this? Is there a workaround?

[zhengming]

Marvin,
Thank you! Sorry, I missed to specify but the range is actually fixed, but is I wrote above all date values are descending but every other value contains text.

[martindwilson]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[MarvinP]

Time for an IfError...

IfError(VLOOKUP(A1, $B$1:$C$20, 2, FALSE),"")

Try that as a formula and pull it down.

[zhengming]

I resolved the problem. I tried to simplify the problem - it's quite a big formula with nested and repeating IF, OR, IFERROR, INDEX, MATCH, VLOOKUP functions - hence my vision was blurred and I mislocated the problem - the problem was the in the MATCH and not the VLOOKUP function. I apologize for that but thanks a lot for your suggestions - I found it while trying to create an intelligible sample workbook.