EXCEL Functions Does not Work for Large Data

[nosense]

Hi,
I am having a problem with an Excel 2010. Functions such as lookup, index, match , max, min etc. don't work for whole lot of data.
I have noticed that if I select the data up to approximately third of whole lot the functions seem to be fine.
It's either giving me completely wrong values (for MAX function) or "N/A" message (Vlookup and Index-match). My data is about 158000 rows big.
Is anyone come across this issue before.
Many thanks,
Stan

[Special-K]

I've never heard this before.
Can you post a spreadsheet?

[nosense]

I had to zip it it's over 4MB.
I have highlighted the Max function cells with working small range
and not working one with whole range of data. It also freaks out when I want to plot whole lot.
Cheers

[shg]

=MAX(C5:E65536) works fine for me.

[nosense]

asd..zip

I've never heard this before.
Can you post a spreadsheet?

Sorry,
Please ignore previous file, this one works as has lesser rows.
Here is the larger data one,
Thanks

[shg]

I don't see a problem.

[nosense]

asd..zip
here is the file

[shg]

Still don't see a problem.

[nosense]

I don't see a problem.

That large value for max one is not real entry in the cell as all data are between 40 and 200

[nosense]

That large value for max one is not real entry in the cell as all data are between 40 and 200

Is your max a "69291.558" as well ?

[shg]

Look in B69196.

Row\Col	B
69194	54.33
69195	54.25
69196	69291.6
69197	54.25
69198	54.27

[nosense]

can you please look at that file.
I cannot upload it on the forum to big size.
I have highlighted cells with an issue. The index match seems not work for greaten than (-1) and also is not giving the right value in the second
http://wikisend.com/download/332904/Mk6e i2 CA 47.B.xlsx

[XOR LX]

Hi.

I get a "This file is malicious, and Chrome has blocked it" message when I try to download that file.

Regards

[nosense]

Hi.

I get a "This file is malicious, and Chrome has blocked it" message when I try to download that file.

Regards

My chrome does not block it.
Its just an excel file on safe work machine with Kaspersky
Try to run a scan of file.
Or suggest another way of uploading this.

[Pete_UK]

You should really use absolute row references (i.e. with a $ symbol in front of them) if you are going to copy formulae down. You have this formula in K19:

=VLOOKUP(M19,H4:H80788,1,TRUE)

but this one in K20:

=VLOOKUP(M20,H5:H74926,1,TRUE)

and this in K22:

=VLOOKUP(M22,H5:H80790,1,TRUE)

and in K24:

=INDEX(A4:H80789,MATCH(N4,H4:H80789,1),1)

Notice that all your lookup tables have different ranges.

The main problem, though, is that your data in column H is not sorted, and so you should use FALSE (or 0) instead of TRUE (or 1) in the VLOOKUPs and MATCH functions to look for an exact match - if more than 1 exact matches occur, those functions will return the value that corresponds to the first match only - if there is no exact match then they will return #N/A.

Hope this helps.

Pete

[nosense]

You should really use absolute row references (i.e. with a $ symbol in front of them) if you are going to copy formulae down. You have this formula in K19:

=VLOOKUP(M19,H4:H80788,1,TRUE)

but this one in K20:

=VLOOKUP(M20,H5:H74926,1,TRUE)

and this in K22:

=VLOOKUP(M22,H5:H80790,1,TRUE)

and in K24:

=INDEX(A4:H80789,MATCH(N4,H4:H80789,1),1)

Notice that all your lookup tables have different ranges.

The main problem, though, is that your data in column H is not sorted, and so you should use FALSE (or 0) instead of TRUE (or 1) in the VLOOKUPs and MATCH functions to look for an exact match - if more than 1 exact matches occur, those functions will return the value that corresponds to the first match only - if there is no exact match then they will return #N/A.

Hope this helps.

Pete

Hi,
Try to increase VLOOKUP value in M23 to 156 or more. Then it returns incorrect value (55). I cannot use exact match here as data not necessarily matches lookup number. Similar with INDEX MATCH, why is Greater than does not work (-1)
Cheers Pete,
Stan

[Pete_UK]

You can't use the final parameter of VLOOKUP or MATCH set to TRUE (or 1), as the lookup column HAS to be sorted, and yours isn't.

Pete

[nosense]

You can't use the final parameter of VLOOKUP or MATCH set to TRUE (or 1), as the lookup column HAS to be sorted, and yours isn't.

Pete

Many thanks for your time. Do you know other function that could have help me play with unsorted data?
I wanna find specific value with approximate match and return value on offset cell in a row in an unsorted table.
I wanna find fist value from top if that can help.
Stan

[Pete_UK]

Have you tried it by changing those TRUEs to FALSEs?

Pete

[nosense]

Have you tried it by changing those TRUEs to FALSEs?

Pete

i have but did not pick up value because is some values are different from my look up one...
thats very strange i cannot use greater than (-1) match in index-match functions...

[Pete_UK]

You could have a construct like this in K19:

=IFERROR(VLOOKUP(M19,H$4:H$80790,1,0),IFERROR(VLOOKUP(M19-0.5,H$4:H$80790,1,0),VLOOKUP(M19-1,H$4:H$80790,1,0))

This will try to find an exact match with M19, but if there isn't one it will try to find an exact match with (M19-0.5), and if there still isn't a match it will try (M19-1). You can build on this construct if you want to try smaller increments.

Hope this helps.

Pete

[MrShorty]

From the help file for the MATCH() function: https://support.office.com/en-us/art...9-533f4a37673a

When match_type=1, lookup values must be sorted in ascending order, and MATCH() will find the largest value that is equal to or less then lookup value.
When match_type=-1, lookup values must be sorted in descending order, and MATCH() will find the smallest value that equal to or greater than lookup value.
When match_type=0, lookup values do not need to be sorted, and MATCH() will only find an exact match.

When you want to use match_type 1 or -1, you must sort your lookup values. I gather from Pete_UK's response, that you have not sorted your lookup table. (Like XORLX, my browser blocks the download as a potentially harmful file, so I have not looked at your file myself). Are you unable/unwilling to sort the table?

[nosense]

From the help file for the MATCH() function: https://support.office.com/en-us/art...9-533f4a37673a

When match_type=1, lookup values must be sorted in ascending order, and MATCH() will find the largest value that is equal to or less then lookup value.
When match_type=-1, lookup values must be sorted in descending order, and MATCH() will find the smallest value that equal to or greater than lookup value.
When match_type=0, lookup values do not need to be sorted, and MATCH() will only find an exact match.

When you want to use match_type 1 or -1, you must sort your lookup values. I gather from Pete_UK's response, that you have not sorted your lookup table. (Like XORLX, my browser blocks the download as a potentially harmful file, so I have not looked at your file myself). Are you unable/unwilling to sort the table?

Yes, i cannot sort the table

[nosense]

You could have a construct like this in K19:

=IFERROR(VLOOKUP(M19,H$4:H$80790,1,0),IFERROR(VLOOKUP(M19-0.5,H$4:H$80790,1,0),VLOOKUP(M19-1,H$4:H$80790,1,0))

This will try to find an exact match with M19, but if there isn't one it will try to find an exact match with (M19-0.5), and if there still isn't a match it will try (M19-1). You can build on this construct if you want to try smaller increments.

Hope this helps.

Pete

Pete,
i have also tried that approach

=MIN((IF(N13-$H$4:$H$80789<0,999999,N13-$H$4:$H$80789)))
And still it returns value that the max value in a column were 53 and it is not !

[Pete_UK]

The file has nearly 81,000 rows. It appears to be some kind of data-logger, as column A is used for a time which increments by 0.1 or 0.2 minutes on each row. The next 4 columns have (temperature ?) readings, which are averaged in column H - that's the lookup column for the formulae.

I don't really know what the OP is trying to achieve - I just told him why the formulae are not working.

Pete

EDIT: this was in response to MrShorty's post #22

[nosense]

Attachment 379146Attachment 379146

Please have a look at what i did and try to tell me what is going on. This formula (=MIN(IF(N13-H104:H44182<0,99999,N13-H104:H44182))) seems to work only till certain limited rows. When i change the range it does not return any value. The one it returns when i select range from beginning is incorrect as you can see basing on max value in a column....