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$14 78,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?

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))),"",IN DEX(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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jon Dow" wrote in message
...
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$14 78,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?

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 (persons 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" wrote:

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))),"",IN DEX(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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jon Dow" wrote in message
...
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$14 78,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?

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jon Dow" wrote in message
...
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


"Bernard Liengme" wrote:

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))),"",IN DEX(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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jon Dow" wrote in message
...
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$14 78,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?

Once again this board ROCKS!!!

Thanks it worked.

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jon Dow" wrote in message
...
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


"Bernard Liengme" wrote:

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))),"",IN DEX(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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jon Dow" wrote in message
...
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$14 78,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?

Happy to have been of assistance!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jon Dow" wrote in message
...
Once again this board ROCKS!!!

Thanks it worked.

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jon Dow" wrote in message
...
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


"Bernard Liengme" wrote:

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))),"",IN DEX(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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jon Dow" wrote in message
...
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$14 78,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?