array formula behavior changed?!

[Little_Ghost]

Hi guys!
I have a weird little problem with an array formula.
I've been using this for a couple of years now and it always worked perfect but I just noticed something strange.

This is the formula:

=IF(ISNA(INDEX(DB_Data!$A$2:$F$3550;(MATCH($A9&$C9&$D9;DB_Data!$A$2:$A$3550&DB_Data!$C$2:$C$3550&DB_Data!$D$2:$D$3550;0));5));"Not found in database";INDEX(DB_Data!$A$2:$F$3550;(MATCH($A9&$C9&$D9;DB_Data!$A$2:$A$3550&DB_Data!$C$2:$C$3550&DB_Data!$D$2:$D$3550;0));5))

What it does is look up the values from col. A, C and D in a different (data) worksheet and match them with the data on the formula's row. When found it then return the corresponding value for column E for the data worksheet and put that in column F (where the forumla is).

Now, when col. A, C and D are blank, it always used to put "not found in database" in column F. This was fine but I had to extend te range of where the fomula was looking and when I now copy the formula down, it will read "0" instead of "not found in database".
Whenever I fill anything in in one of the columns'cells it is checking, it will return "not found in database" (when it's not on the other worksheet ofcourse).

Does anybody have an idea why it is now doing this?

Kind Regards,

- Little_Ghost

 

[martindwilson]

that seems to be correct , as that does match
however i'd probably use a non array version
=IF(ISNA(MATCH($A9&$C9&$D9,INDEX(DB_Data!$A$2:$A$3550&DB_Data!$C$2:$C$3550&DB_Data!$D$2:$D$3550,0),0)),"not found in data base",INDEX(DB_Data!$E$2:$E$3550,MATCH($A9&$C9&$D9,INDEX(DB_Data!$A$2:$A$3550&DB_Data!$C$2:$C$3550&DB_Data!$D$2:$D$3550,0),0)))

 

[Little_Ghost]

that seems to be correct , as that does match
however i'd probably use a non array version
=IF(ISNA(MATCH($A9&$C9&$D9,INDEX(DB_Data!$A$2:$A$3550&DB_Data!$C$2:$C$3550&DB_Data!$D$2:$D$3550,0),0)),"not found in data base",INDEX(DB_Data!$E$2:$E$3550,MATCH($A9&$C9&$D9,INDEX(DB_Data!$A$2:$A$3550&DB_Data!$C$2:$C$3550&DB_Data!$D$2:$D$3550,0),0)))

Okay, clear, though the correct formula should be

=IF(ISNA(MATCH($A1&$C1&$D1;INDEX(DB_Data!$A$2:$A$6500&DB_Data!$C$2:$C$6500&DB_Data!$D$2:$D$6500;0);0));"not found in data base";INDEX(DB_Data!$E$2:$E$6500;MATCH($A1&$C1&$D1;INDEX(DB_Data!$A$2:$A$6500&DB_Data!$C$2:$C$6500&DB_Data!$D$2:$D$6500;0);0)))

"," should have been ";".

Still, it returns "0" when cell A, C or D is empty (aka when it doesn't have anything to look up).

 

[Little_Ghost]

And I just figured out that it's not getting the correct info.... :S
I started with 1 problem, now I have several .... :S

 

[martindwilson]

"," should have been ";". nope works for me but thats your regional settings so use what you have.
if you are getting 0 its found a match
find out where with just

=MATCH($A1&$C1&$D1;INDEX(DB_Data!$A$2:$A$6500&DB_Data!$C$2:$C$6500&DB_Data!$D$2:$D$6500;0) it will tell you the row number
there is a possibility that the concatenated range is there twice
eg
1 12 1
1 1 21 both produce 1121

 

[Aladin Akyurek]

And I just figured out that it's not getting the correct info.... :S
I started with 1 problem, now I have several .... :S

What is the version of Excel you are on? Is the result always text if the look up is successful?

 

[martindwilson]

also 3 blanks will match 3 blanks

Sheet1

*	A	B	C	D	E	F	G	H	I
1	1	12	1	m	*	1	1	21	m
2	1	1	21	m	*	1	12	1	w
3	a	ab	c	X	*	A	A	BC	X
4	AAB	*	C	X	*	*	*	*	NOWT
5	*	*	*	NOWT	*	*	*	*	*

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D1 =INDEX($I$1:I$4,MATCH(A1&B1&C1,INDEX($F$1:$F$4&$G$1:$G$4&$H$1:$H$4,0),0)) D2 =INDEX($I$1:I$4,MATCH(A2&B2&C2,INDEX($F$1:$F$4&$G$1:$G$4&$H$1:$H$4,0),0)) D3 =INDEX($I$1:I$4,MATCH(A3&B3&C3,INDEX($F$1:$F$4&$G$1:$G$4&$H$1:$H$4,0),0)) D4 =INDEX($I$1:I$4,MATCH(A4&B4&C4,INDEX($F$1:$F$4&$G$1:$G$4&$H$1:$H$4,0),0)) D5 =INDEX($I$1:I$4,MATCH(A5&B5&C5,INDEX($F$1:$F$4&$G$1:$G$4&$H$1:$H$4,0),0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


you can get around duplicate concatenations by adding a separator
Sheet1

*	A	B	C	D	E	F	G	H	I
1	1	12	1	w	*	1	1	21	m
2	1	1	21	m	*	1	12	1	w
3	a	ab	c	#N/A	*	A	A	BC	X
4	AAB	*	C	#N/A	*	*	*	*	NOWT
5	A	A	BC	X	*	*	*	*	*
6	*	*	*	NOWT	*	*	*	*	*

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D1 =INDEX($I$1:I$4,MATCH(A1&"-"&B1&"-"&C1,INDEX($F$1:$F$4&"-"&$G$1:$G$4&"-"&$H$1:$H$4,0),0)) D2 =INDEX($I$1:I$4,MATCH(A2&"-"&B2&"-"&C2,INDEX($F$1:$F$4&"-"&$G$1:$G$4&"-"&$H$1:$H$4,0),0)) D3 =INDEX($I$1:I$4,MATCH(A3&"-"&B3&"-"&C3,INDEX($F$1:$F$4&"-"&$G$1:$G$4&"-"&$H$1:$H$4,0),0)) D4 =INDEX($I$1:I$4,MATCH(A4&"-"&B4&"-"&C4,INDEX($F$1:$F$4&"-"&$G$1:$G$4&"-"&$H$1:$H$4,0),0)) D5 =INDEX($I$1:I$4,MATCH(A5&"-"&B5&"-"&C5,INDEX($F$1:$F$4&"-"&$G$1:$G$4&"-"&$H$1:$H$4,0),0)) D6 =INDEX($I$1:I$4,MATCH(A6&"-"&B6&"-"&C6,INDEX($F$1:$F$4&"-"&$G$1:$G$4&"-"&$H$1:$H$4,0),0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Little_Ghost]

Okay, this is getting really confusing really fast.
I've uploaded 2 demo files:
1 of the old situation (with properly working array formula (demo_file_old.xls))
1 if the new situation (with not properly working array formula (demo_file_new.xls))

I hope this clarifies things a little.
The excel version used is excel 2010

 

[martindwilson]

that link downloaded some .exe file

 

[martindwilson]

ok ive looked at those 2 files ?cannot see what the problem is.it will return 0 your range includes blanks at the end your index
DB_Data!$A$1:$F$35 is blank from row 26
using your (unnecesarry) array {=MATCH($A22&$C22&$D22,DB_Data!$A$1:$A$35&DB_Data!$C$1:$C$35&DB_Data!$D$1:$D$35,0)} returns row 26 which in the index is null but =null in a formula returns 0 excel will return 0 for a null unless you tell it otherwise
as i said once you concatenate stuff you create a string $A22&$C22&$D22 though it appears there is nothing in there its enough for the match to work with the
concatenated DB_Data!$A$1:$A$35&DB_Data!$C$1:$C$35&DB_Data!$D$1:$D$35