Converting Lotus VLOOKUP to Excel 2000

[DebbieDavis]

Hi there,

I have the following that works in the Lotus spreadsheet
but when brought over to Excel, produces the famous
#VALUE! error.

@VLOOKUP(J2,Q10..S14,2)

It converts to:

=VLOOKUP(J2,Q10:S14,2)

J2 is a date, 4/1/2003 or @date(2003,4,1)

Q10:S14

NEXT PAY CHG BALANCE NEW PAYMENT
- - -
05/20/2003 9,423.66 758.09
06/20/2003 8,979.88 722.39
07/20/2003 8,533.23 686.46
08/20/2003 8,083.71 650.30
09/20/2003 7,631.27 613.90

The result is supposed to be 613.90

I haven't moved any data. I just opened it in Excel. Thanks in advance for your thoughts.

 

[xlbo]

I think you need the 4th argument and it needs to be 3 not 2 to return 613.90 for 09/20/2003 - try:
=VLOOKUP(J2,$Q$10:$S$14,3,FALSE)
where J2 holds 09/20/2003 Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[DebbieDavis]

I hate to bother you guys again, but using this formula, what happens when 2004 rolls around?

=DATE(YEAR(P20),MONTH(P20)+1,DAY(P20))

P20 is date(yyyy,mm,dd)

I'm running into that problem now and getting #NUM!. Thanks again.

 

[xlbo]

Shouldn't make any difference - the formula should cope with it. What date are you entering to get the ~NUM! error? Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde