Index returning #N/A

J

Jon Dow

I have this very long (probably can be shortened but it is the only way I
know to do it) formula that look and matches 2 criteria. Here is the formula:

{=IF(INDEX(ST!$G$7:$G$1478,MATCH($A6&$G$2,ST!$E$7:$E$1478&ST!$D$7:$D$1478,0))<=1,"",INDEX(ST!$G$7:$G$1478,MATCH($A6&$G$2,ST!$E$7:$E$1478&ST!$D$7:$D$1478,0)))}

In a quick nutshell ST! is the name of the sheet. A6 is the name I am
looking up and G2 is the month that it is matching.

What I want it to do is to lookup the name and month to pull out the sales
figure. If it does not find it, I want the result to be 0 or the word Zero
instead of the #N/A that comes up. Or leave it blank by the "". The #N/A mess
up all my totals on the sheet.

Is there an easy (easier) formula to make this happen?
 
B

Bernard Liengme

On Sheet2 I have this
****** Jan Feb Mar
alice 83 22 57
fred 137 125 193
george 132 112 33


The names are in A1:A8; the months in B1:M1; numbers in B1:M8

On Sheet1 in A1 I have a name (say Fred) , in B1 a month (say Mar)
In C1 this formula
=INDEX(Sheet2!$B$2:$M$4,MATCH(A1,Sheet2!$A$2:$A$8,0),MATCH(B1,Sheet2!$B$1:$M$1,0))
returns the intersection of the month and name (here it is 33)

If I now modify this to
=IF(ISERROR(the_Index_formula),"",the_Index-fromula)
I believe we get what you want
Here it is
=IF(ISERROR(INDEX(Sheet2!$B$2:$M$8,MATCH(A1,Sheet2!$A$2:$A$8,0),MATCH(B1,Sheet2!$B$1:$M$1,0))),"",INDEX(Sheet2!$B$2:$M$8,MATCH(A1,Sheet2!$A$2:$A$8,0),MATCH(B1,Sheet2!$B$1:$M$1,0)))

Of course, the use of named ranges makes this a easier on the eyes
=IF(ISERROR(INDEX(mydata,MATCH(A2,mynames,0),MATCH(B2,mymonths,0))),"",INDEX(mydata,MATCH(A2,mynames,0),MATCH(B2,mymonths,0)))
best wishes
 
J

Jon Dow

Thanks for your reply. I could not get this to work. I have simplified this
for this posting. I have in columns D1:F5 the following:

Month Name Sold
Jan James 500
Jan Woods 850
Feb Smith 165
Feb James 600

I have named D2:D5 Month, E2:E5 person, and D2:F5, mylist. What I am trying
to do is to get Excel to match A6 (person’s name) and G2 (month) to pull out
the correct number. So if A6 is Woods and G2 is Jan, I want 850 returned or
if it does not match I want "" instead of #N/A. Here is the formula I am
using to do this:

{=IF(ISERROR(INDEX(MyList,MATCH(A6,Person,0),MATCH(G2,Month,0))),"",INDEX(MyList,MATCH(A6,Person,0),MATCH(G2,Month,0)))}

Am I missing something because it returns "" for all of them. Thanks for
your help.

Jon
 
B

Bernard Liengme

This approach cannot work since the entries in Month are not unique. With G2
holding Jan, MATCH(G2,Month,0) will awls give the value 1 since that is the
first occurrence of Jan

Let's give up on INDEX and use SUMPRODUCT
=SUMPRODUCT(--(Person=A6),--(Month=G2),Sold)
I named F2:F5 as "sold"

This will give you 850 for Jan/Woods as required. But with Jan/Fred is gives
0
To get #N/A when there is not match use
=IF(SUMPRODUCT(--(Person=A6),--(Month=G2))=0,NA(),SUMPRODUCT(--(Person=A6),--(Month=G2),Sold))

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

In you want to use ranges then
=SUMPRODUCT(--(E2:E5=A6),--(D2:D5=G2),F2:F5)
Only in Excel 2007 can you use full column references
=SUMPRODUCT(--(E:E=A6),--(D:D=G2),F:F)

best wishes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top