vlookup hassles...

[Deepak]

Hi

i use VLOOKUP formula on around 600 records.

It works perfectly fine but after 350 records, the values are undesirable.

Please help.

What i am trying to this is:

Sheet-1
A D E I
1 Phoneno Activedt RechargeDt Autonumber
2 1723091070 29-Apr 29-Apr 2
3 1723091070 29-Apr 11-May 3
4 1723091070 29-Apr 11-Jun 4
5 1723093698 30-Apr 14-Jun 859
6 1723093756 28-Apr 28-Apr 5
7 1723093756 28-Apr 16-Jun 6

I need to do the following:
(i) Sort record to get the last recharge date for a particular phoneno
eg. 1723091070 last recharge on 11-Jun
(ii) Enter that last recharge date using vlookup formula on next sheet.

Currently i use the following formula to get the last rechargedate:

=vlookup(a2,sheet1!A$1:I$1000,5,max(sheet1!I$1:I$1000)
where 5 = column E.

using this i get good results till record 350, but not after that.

Please help, its' urgent

 

[Aladin Akyurek]

Try:

=MAX(IF(PhoneRange=F2,RechargeRange))

which must be confirmed with control+shift+enter instead of just with enter.

PhoneRange refers to the range housing the phone numbers on Sheet1,
RechargeRanges to the range housing the recharge dates on Sheet1.

 

[Stephen Dunn]

Hi Deepak,

This is taken directly from the help file on VLOOKUP():


*******************************************************

Range_lookup is a logical value that specifies whether you want VLOOKUP to
find an exact match or an approximate match. If TRUE or omitted, an
approximate match is returned. In other words, if an exact match is not
found, the next largest value that is less than lookup_value is returned. If
FALSE, VLOOKUP will find an exact match. If one is not found, the error
value #N/A is returned.

Remarks

If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the
largest value that is less than or equal to lookup_value.


If lookup_value is smaller than the smallest value in the first column of
table_array, VLOOKUP returns the #N/A error value.


If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP
returns the #N/A value.

*******************************************************


range_lookup refers to the last value in the VLOOKUP. You are having some
success because any value that isn't 0 is taken as TRUE (0 is FALSE). So,
xl reads your formula as:

=vlookup(a2,sheet1!A$1:I$1000,5,TRUE)

So, as long as your list is sorted correctly, and any number looked up is
within the list, you will get a result. However, if a number that you look
up is not in the list, or not in a sorted position within the list, then the
result you will get is from the row that contains "the next largest value
that is less than lookup_value".

Follow Aladin's advice.