• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match strange behaviour - odd issue

SirJB7

SirJB7

Excel Rōnin
Hi!


A few moments ago while trying to find another approach for a question posted at this topic

http://chandoo.org/forums/topic/if-formula-criteria-in-three-cells

I found myself faced to a more than strange Excel MATCH function behaviour.


Give a look at this file:

https://dl.dropbox.com/u/60558749/If%20formula%20-%20criteria%20in%20three%20cells%20%28for%20Eben%20at%20chandoo.org%29.xlsx


Go to column G, confirm firstly that cells G3, G4 & G6 display an error #¡VALUE!, and then try to evaluate them from the edit bar, doing:

- select any of them, let's say G3

- press F2

- from the edit bar select select the whole formula excluding the equal sign

- press F9 for evaluate the selected expression

- write down the displayed value

- press Escape to quit edition without any changes


Now...


If you wrote down a 3 (three) -or a 2 for G4 or a 1 for G6-, would you be as kind as to explain me why do you get an error value if when evaluating the whole formula cell you get the numeric and expected value?


I checked MATCH function documentation against built-in and online help just in case it works only with cell ranges and not arrays, but it the examples provided there there are both kind of references: ranges and arrays.


Keen to read about it!


Regards!


PS: for reference purposes, I use an Excel 2010 Spanish version.
 
Is this not due to the SIGN function not working on a range rather rather than MATCH?


If you enter =MATCH(1,SIGN($A3:$C3),0) as an array it seems to calculate ok.
 
Hi, dohsan!

Yes, it works as an array formula... but why does it evaluate correctly as a normal formula?

That's the annoying point.

Regards!
 
Hi!


Being a rainy Saturday, in fact raining since Monday almost continually, getting out of space in my 2 1Tb WD Caviar Black, in my WD external 500Gb and in my girl friend's similar too, I decided to do a little of housekeeping.


Music was Ok, movies were Ok, photos & videos... not Ok at all. Actually very well organized by dates in folders with structured names, but with partial copies or backups in every device available, including 2 16Gb pendrives, 2 32Gb micro SD,...


I started manually... half an hour later I was playing backgammon with my friend Carlsberg at a hand.


Ok, gonna do something in Excel to match 2 folders. It was very simple since I adapted my Excel files Documenting & inspector (http://chandoo.org/forums/topic/excel-files-documenting-inspector) cropping the workbook's stuff and duplicating ranges for both folders.


The idea was (and still is) to get a formula for each list that uses MATCH function to retrieve the row number related to the other list where an item is found or zero, and maybe INDEX after to visually match lists without reordering (this is still pending). Yes, I know, simply and stupid, like the best ideas, those bullet proof... well, this is again all full of holes. MATCH again, and this time with non-array formula.


Give a look at this file:

https://dl.dropbox.com/u/60558749/Folder%20%26%20files%20matching%20%28from%20SirJB7%20at%20chandoo.org%29.xlsm


The issue arises in columns J and U:

=SI.ERROR(COINCIDIR(A2&"_"&B2&"_"&C2;L:L&"_"&M:M&"_"&N:N;0);0) -----> in english: =IFERROR(MATCH(A2&"_"&B2&"_"&C2,L:L&"_"&M:M&"_"&N:N,0),0)

=SI.ERROR(COINCIDIR(L2&"_"&M2&"_"&N2;A:A&"_"&B:B&"_"&C:C;0);0) -----> in english: =IFERROR(MATCH(L2&"_"&M2&"_"&N2,A:A&"_"&B:B&"_"&C:C,0),0)


Formulas always display a 0 (zero), so matching shouldn't be found, but is exists! In the sample file the first 10 rows are equal and the 11th of first range is unpaired, so in this only case it should display zero.


Even more, and this is the same issue as this original topic I started so I continued the thread, if I evaluate the MATCH part of the formula it displays the correct value!


I'm unable to see the hidden elephant. Any help would be highly appreciated.


Regards!


PS: The idea is to use column W as the result indicator, but it always display error ("X") as per the J and U columns wrong zeroes, so I added a temporary result in X column.


PS 2: the "actions" feature it's still at developing process... yes, I'm lazy, so why going to the Windows Explorer to copy, move or delete files if I could do it from there? :p
 
In J2:
Code: 
=IFERROR(MATCH(A2&"_"&B2&"_"&C2,L2:L12&"_"&M2:M12&"_"&N2:N12,0),0)
Ctrl+Shift+Enter


Don't use entire columns
 
Hi, Hui!


I'd swear I started with the array formula and as retrieved the correct value when evaluating and wrong when entered as array, I left it as non-array... but now I'm doubting about all. :(


As I wrote, I knew there was an elephant near, thank you very much for pointing it out so kindly.


Regards!


PS: BTW, with 2 lists of 5800 and 4900 rows since I began this reply it only calculated 7% of the sheet using the 8 processors (cores)... so gonna change the approach.


PS 2: almost sure I'll create 2 helper hidden columns with A&B&C and J&K&L and I'll setup a simple non-array MATCH; will post finished file in a new topic, maybe it'd be useful for others.
 
MATCH doesn't have built-in ability to coerce ARRAY but LOOKUP does. So even when the syntax is correct (conceptually that is) as in your case the formula doesn't work.


So in your file match case in Cell U2:

=LOOKUP(L2&M2&N2,$A$1:$A$12&$B$1:$B$12&$C$1:$C$12,ROW($B$1:$B$12))

And in Cell J2:

=LOOKUP(A2&B2&C2,$L$1:$L$12&$M$1:$M$12&$N$1:$N$12,ROW($L$1:$L$12))

shall work which is normal formula. I'd hope it will fare better than MATCH array entered. So your feedback will be important.
 
Hi, shrivallabha!


Everyday I learn something new. Very nice and interesting the approach of the usage of function LOOKUP (in spanish BUSCAR)... there's a long time I didn't use it, in fact I don't remember doing so in the last years...


And curiously and incredibly in the built-in help of 2010 version (and I'm almost sure that in 2007 too, and I'm doubting about 2003) it says that "this function is provided for compatibility with older versions", so users were leaded towards using the "normal" and "of course better as newer and supposedly more powerful" (I include myself) VLOOKUP and HLOOKUP (in spanish, BUSCARV and BUSCARH).


I just had to make a little change in the formulas as they didn't worked starting from row 1 but yes starting from row 2. Despite of this, they worked like a charm, both as you indicated and with the underscores as in my original question. In the re-uploaded file I used seldom versions for columns J and U.


Thank you very much again, shrivallabha!


Regards!
 
As a comment on the built in Help


2007 was terribly documented

2010 is much much better

2013 is great so far, but not yet complete and appears so far to be only available online
 
@Hui

Hi!

Didn't checked it yet, but... do you know if 2013 mentions DATEDIF function? And if it does, anything about "m" parameter?

Regards!
 
My post has disappeared! So SirJB7 it seems you are talking to an invisible man. So BUSCAR it is, i'd hope it was faster than any BUS-CAR that SirJB7 uses.


And maybe this can fill in shoes for the file at first post:

a replacement for array entered =MATCH(1,SIGN($A3:$C3),0) would be:

=LOOKUP(1,1/SIGN(A3:C3),COLUMN(A3:C3))
 
@shrivallabha

Hi!

Somehow marked as spam, I fixed it yet. Against a visible man :)

Regards!

PS: I'll check later your last comment and write back to you.
 
You must log in or register to reply here.
Back
Top