Index returning #N/A

[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?

 

[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

 

[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 D25 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

 

[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),--(D25=G2),F2:F5)
Only in Excel 2007 can you use full column references
=SUMPRODUCT(--(E:E=A6),--(D=G2),F:F)

best wishes

 

[Jon Dow]

Once again this board ROCKS!!!

Thanks it worked.

 

[Bernard Liengme]

Happy to have been of assistance!