array formula behavior changed?!

Little_Ghost · Mar 8, 2013

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:

Code:

=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 · Mar 8, 2013

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 · Jul 24, 2013

martindwilson said:
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

Code:

=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 · Jul 24, 2013

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 · Jul 24, 2013

"," 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 · Jul 24, 2013

Little_Ghost said:
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 · Jul 24, 2013

also 3 blanks will match 3 blanks

Sheet1

*	A	B	C	D	E	F	G	H	I
m	*	m
m	*	w
a	ab	c	X	*	A	A	BC	X
AAB	*	C	X	*	*	*	*	NOWT
*	*	*	NOWT	*	*	*	*	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]21[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]21[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

</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))

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
w	*	m
m	*	w
a	ab	c	#N/A	*	A	A	BC	X
AAB	*	C	#N/A	*	*	*	*	NOWT
A	A	BC	X	*	*	*	*	*
*	*	*	NOWT	*	*	*	*	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]21[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]21[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

</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))

Excel tables to the web >> Excel Jeanie HTML 4

Little_Ghost · Jul 25, 2013

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 · Jul 25, 2013

that link downloaded some .exe file

martindwilson · Jul 25, 2013

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

array formula behavior changed?!

Little_Ghost

New Member

martindwilson

Well-known Member

Little_Ghost

New Member

Little_Ghost

New Member

martindwilson

Well-known Member

Aladin Akyurek

MrExcel MVP

martindwilson

Well-known Member

Little_Ghost

New Member

martindwilson

Well-known Member

martindwilson

Well-known Member

Similar threads

Share this page

array formula behavior changed?!

New Member

Well-known Member

New Member

New Member

Well-known Member

MrExcel MVP

Well-known Member

New Member

Well-known Member

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock