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

Aladin Akyurek · Jul 25, 2013

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

The MATCH bit...

MATCH($A22&$C22&$D22,DB_Data!$A$1:$A$35&DB_Data!$C$1:$C$35&DB_Data!$D$1:$D$35,0)

succeeds. The formula proceeds to fetch from an empty cell, Hence 0.

By the way, you have an inefficient set up...

In H1 on updates, control+shift+enter, not just enter, and copy down:

=IF(ISNUMBER(MATCH(A1&"|"&C1&"|"&D1,DB_Data!$A$1:$A$25&"|"&DB_Data!$C$1:$C$25&"|"&DB_Data!$D$1:$D$25,0)),MATCH(A1&"|"&C1&"|"&D1,DB_Data!$A$1:$A$25&"|"&DB_Data!$C$1:$C$25&"|"&DB_Data!$D$1:$D$25,0),"")

In E1 just enter and copy down:

Either...

=IF($H1="","",INDEX(DB_Data!$A$1:$F$25,$H1,2))

Or...

IF($H1="","Not found in the database",INDEX(DB_Data!$A$1:$F$25,$H1,2))

In F1 just enter and copy down:

=IF($H1="","",INDEX(DB_Data!$A$1:$F$25,$H1,5))

In G1 just enter and copy down:

=IF($H1="","",INDEX(DB_Data!$A$1:$F$25,$H1,6))

If you are on a post 2003 system, modify H1 to:

=IFERROR(MATCH(A1&"|"&C1&"|"&D1,DB_Data!$A$1:$A$25&"|"&DB_Data!$C$1:$C$25&"|"&DB_Data!$D$1:$D$25,0),"")

If you extend the ranges to cover empty cells, the formulas will behave unexpectedly.

Remedy:

H1, control+shift+enter:

=IF(LEN(A22&"|"&C22&"|"&D22)>2,IF(ISNUMBER(MATCH(A22&"|"&C22&"|"&D22,DB_Data!$A$1:$A$26&"|"&DB_Data!$C$1:$C$26&"|"&DB_Data!$D$1:$D$26,0)),MATCH(A22&"|"&C22&"|"&D22,DB_Data!$A$1:$A$26&"|"&DB_Data!$C$1:$C$26&"|"&DB_Data!$D$1:$D$26,0),""),"")

The formula could be too long on 2003...

martindwilson · Jul 25, 2013

yeh like i said

Little_Ghost · Jul 25, 2013

martindwilson said:
... I cannot see what the problem is.it will return 0 your range includes blanks at the end ....

It doesn't do that in the "_old" file. Even if the A till E columns are empty it will show "Not found" and mark the line red.

Little_Ghost · Jul 25, 2013

Okay, I got these to work though it does
A) show the line number where it found the item
B) now I have to change 4 formula's when the DB_data sheet gets even longer (could probably solve this by setting the rage to end at 1,048,576).
C) still the old problem, if A till E are empty, it will show 0. It used to show "Not found.." with the line marked red.

markmzz · Jul 25, 2013

Look at this:

Code:

Your formula in the Old file
=IF(ISNA(INDEX(DB_Data!$A$1:$F$[COLOR=#ff0000]25[/COLOR],(MATCH($A1&$C1&$D1,DB_Data!$A$1:$A$[COLOR=#ff0000]25[/COLOR]&DB_Data!$C$1:$C$[COLOR=#ff0000]25[/COLOR]&DB_Data!$D$1:$D$[COLOR=#ff0000]25[/COLOR],0)),2)),"Not found in database",
INDEX(DB_Data!$A$1:$F$[COLOR=#ff0000]25[/COLOR],(MATCH($A1&$C1&$D1,DB_Data!$A$1:$A$[COLOR=#ff0000]25[/COLOR]&DB_Data!$C$1:$C$[COLOR=#ff0000]25[/COLOR]&DB_Data!$D$1:$D$[COLOR=#ff0000]25[/COLOR],0)),2))

Your formula in the New file
=IF(ISNA(INDEX(DB_Data!$A$1:$F$[COLOR=#ff0000]35[/COLOR],(MATCH($A1&$C1&$D1,DB_Data!$A$1:$A$[COLOR=#ff0000]35[/COLOR]&DB_Data!$C$1:$C$[COLOR=#ff0000]35[/COLOR]&DB_Data!$D$1:$D$[COLOR=#ff0000]35[/COLOR],0)),2)),"Not found in database",
INDEX(DB_Data!$A$1:$F$[COLOR=#ff0000]35[/COLOR],(MATCH($A1&$C1&$D1,DB_Data!$A$1:$A$[COLOR=#ff0000]35[/COLOR]&DB_Data!$C$1:$C$[COLOR=#ff0000]35[/COLOR]&DB_Data!$D$1:$D$[COLOR=#ff0000]35[/COLOR],0)),2))

In the Old file you have a reference for 25 rows in the formula and in the New file you have a reference for 35 rows in the formula. In the New file the formula is working ok, 
it find blank, blank, blank and give you 0, 0, 0 like Aladin said.

If you change the reference in the formula in the Old file to 35, it will give to you 0,0,0 too.

Markmzz

Little_Ghost · Jul 25, 2013

markmzz said:

Look at this:

Code:

Your formula in the Old file
=IF(ISNA(INDEX(DB_Data!$A$1:$F$[COLOR=#ff0000]25[/COLOR],(MATCH($A1&$C1&$D1,DB_Data!$A$1:$A$[COLOR=#ff0000]25[/COLOR]&DB_Data!$C$1:$C$[COLOR=#ff0000]25[/COLOR]&DB_Data!$D$1:$D$[COLOR=#ff0000]25[/COLOR],0)),2)),"Not found in database",
INDEX(DB_Data!$A$1:$F$[COLOR=#ff0000]25[/COLOR],(MATCH($A1&$C1&$D1,DB_Data!$A$1:$A$[COLOR=#ff0000]25[/COLOR]&DB_Data!$C$1:$C$[COLOR=#ff0000]25[/COLOR]&DB_Data!$D$1:$D$[COLOR=#ff0000]25[/COLOR],0)),2))

Your formula in the New file
=IF(ISNA(INDEX(DB_Data!$A$1:$F$[COLOR=#ff0000]35[/COLOR],(MATCH($A1&$C1&$D1,DB_Data!$A$1:$A$[COLOR=#ff0000]35[/COLOR]&DB_Data!$C$1:$C$[COLOR=#ff0000]35[/COLOR]&DB_Data!$D$1:$D$[COLOR=#ff0000]35[/COLOR],0)),2)),"Not found in database",
INDEX(DB_Data!$A$1:$F$[COLOR=#ff0000]35[/COLOR],(MATCH($A1&$C1&$D1,DB_Data!$A$1:$A$[COLOR=#ff0000]35[/COLOR]&DB_Data!$C$1:$C$[COLOR=#ff0000]35[/COLOR]&DB_Data!$D$1:$D$[COLOR=#ff0000]35[/COLOR],0)),2))

In the Old file you have a reference for 25 rows in the formula and in the New file you have a reference for 35 rows in the formula. In the New file the formula is working ok, 
it find blank, blank, blank and give you 0, 0, 0 like Aladin said.

If you change the reference in the formula in the Old file to 35, it will give to you 0,0,0 too.

Markmzz

Yes, I know that but it started off with the formula and behavior in the old file. Then the range got extended and extended and it kept working (somehow).
Then at a certain point the range had to be extended again, and then it stopped working and started putting in the 0.

BUT! I just thought of something: somewhere in between we switched Win version and Office version....

markmzz · Jul 25, 2013

Little_Ghost said:
Yes, I know that but it started off with the formula and behavior in the old file. Then the range got extended and extended and it kept working (somehow).
Then at a certain point the range had to be extended again, and then it stopped working and started putting in the 0.

BUT! I just thought of something: somewhere in between we switched Win version and Office version....

Try this:

In your New file do this:

In the sheet DB_Data, in the cells B26, E26 and F26 type the word Test, then look at columns E, F and G of the sheet Updates. You will find Test,Test,Test and not 0,0,0.

Markmzz

Aladin Akyurek · Jul 25, 2013

Little_Ghost said:
Okay, I got these to work though it does
A) show the line number where it found the item
B) now I have to change 4 formula's when the DB_data sheet gets even longer (could probably solve this by setting the rage to end at 1,048,576).
C) still the old problem, if A till E are empty, it will show 0. It used to show "Not found.." with the line marked red.

As I said earlier, you'll need an efficient set up for such big ranges...

https://dl.dropboxusercontent.com/u/65698317/effcient-setup Little_Ghost.xls

markmzz · Jul 25, 2013

If you don't want the zeros, then try this formulas in your New file:

Code:

E1-> =IF($A1&$C1&$D1="","Not found in database",
IF(ISNUMBER(MATCH($A1&$C1&$D1,INDEX(DB_Data!$A$1:$A$1000&DB_Data!$C$1:$C$1000&DB_Data!$D$1:$D$1000,),0)),
INDEX(DB_Data!$A$1:$F$1000,(MATCH($A1&$C1&$D1,INDEX(DB_Data!$A$1:$A$1000&DB_Data!$C$1:$C$1000&DB_Data!$D$1:$D$1000,),0)),2),
"Not found in database"))

F1-> =IF($A1&$C1&$D1="","Not found in database",
IF(ISNUMBER(MATCH($A1&$C1&$D1,INDEX(DB_Data!$A$1:$A$1000&DB_Data!$C$1:$C$1000&DB_Data!$D$1:$D$1000,),0)),
INDEX(DB_Data!$A$1:$F$1000,(MATCH($A1&$C1&$D1,INDEX(DB_Data!$A$1:$A$1000&DB_Data!$C$1:$C$1000&DB_Data!$D$1:$D$1000,),0)),5),
"Not found in database"))

G1-> =IF($A1&$C1&$D1="","Not found in database",
IF(ISNUMBER(MATCH($A1&$C1&$D1,INDEX(DB_Data!$A$1:$A$1000&DB_Data!$C$1:$C$1000&DB_Data!$D$1:$D$1000,),0)),
INDEX(DB_Data!$A$1:$F$1000,(MATCH($A1&$C1&$D1,INDEX(DB_Data!$A$1:$A$1000&DB_Data!$C$1:$C$1000&DB_Data!$D$1:$D$1000,),0)),6),
"Not found in database"))

Markmzz

Little_Ghost · Jul 25, 2013

Aladin Akyurek said:
As I said earlier, you'll need an efficient set up for such big ranges...

https://dl.dropboxusercontent.com/u/65698317/effcient-setup Little_Ghost.xls

I was already experimenting with that setup

array formula behavior changed?!

Little_Ghost

New Member

Aladin Akyurek

MrExcel MVP

martindwilson

Well-known Member

Little_Ghost

New Member

Little_Ghost

New Member

markmzz

MrExcel MVP

Little_Ghost

New Member

markmzz

MrExcel MVP

Aladin Akyurek

MrExcel MVP

markmzz

MrExcel MVP

Little_Ghost

New Member

Similar threads

Share this page

array formula behavior changed?!

New Member

MrExcel MVP

Well-known Member

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

New 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