array formula behavior changed?!

Little_Ghost

New Member
Joined
Dec 13, 2010
Messages
48
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
 
Last edited:

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

martindwilson

Well-known Member
Joined
Aug 14, 2009
Messages
824
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

New Member
Joined
Dec 13, 2010
Messages
48
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).
 
Last edited:

Little_Ghost

New Member
Joined
Dec 13, 2010
Messages
48
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

Well-known Member
Joined
Aug 14, 2009
Messages
824
"," 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
 
Last edited:

martindwilson

Well-known Member
Joined
Aug 14, 2009
Messages
824
also 3 blanks will match 3 blanks

Sheet1

*ABCDEFGHI
11121m*1121m
21121m*1121w
3aabcX*AABCX
4AAB*CX****NOWT
5***NOWT*****

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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

*ABCDEFGHI
11121w*1121m
21121m*1121w
3aabc#N/A*AABCX
4AAB*C#N/A****NOWT
5AABCX*****
6***NOWT*****

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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
 
Last edited:

martindwilson

Well-known Member
Joined
Aug 14, 2009
Messages
824
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,810
Messages
5,483,044
Members
407,375
Latest member
achusp

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top