#num Error index, match
 

I have a formula that the newsgroup help me with over a year ago. It worked
perfectly and I use it though out my programs. However I want to use it in a
similar situation and I cannot get it to work. The formula returns #num. it
should return 723,314.70

The range name is alabamasort. It is all in 1 worksheet different tabs.
I want the formula to look in the range name alabamasort find TAX BILLED in
column 1 and TOTAL in column 2 and return the value in column 4

The columns are as follows:
Type City/County State amount

Taxble sales cottonwood al 1,303.14
TAX BILLED TOTAL AL 723,314.70

This is the formual

=IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=" TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=" TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4))

Can you help?

#num Error index, match
 

Hi!

The formula you posted has a leading space in " Tax Billed". Is that
supposed to be there? Other than that, your formula works on my end. You can
shorten it slightly:

=IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)="TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4))

You might be able to use this much shorter alternative (normally entered,
not an array):

=SUMPRODUCT(--(INDEX(alabamasort,,1)="Tax
Billed"),--(INDEX(alabamasort,,2)="Total"),INDEX(alabamasort, ,4))

Biff

"taxmom" wrote in message
...
I have a formula that the newsgroup help me with over a year ago. It worked
perfectly and I use it though out my programs. However I want to use it
in a
similar situation and I cannot get it to work. The formula returns #num.
it
should return 723,314.70

The range name is alabamasort. It is all in 1 worksheet different tabs.
I want the formula to look in the range name alabamasort find TAX BILLED
in
column 1 and TOTAL in column 2 and return the value in column 4

The columns are as follows:
Type City/County State amount

Taxble sales cottonwood al 1,303.14
TAX BILLED TOTAL AL 723,314.70

This is the formual

=IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=" TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="
TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4))

Can you help?

#num Error index, match
 

Hi Biff,

If I enter it as an array I still get the #num!, if I just enter it I get
zero data. I can't figure out why its not working. I made sure the 1st two
columns were text and the 4th column number. The data was copied from
Monarch so the space in front is there from the report so I kept it there. I
even typed in the the labels right under the copied over data, fixed the
formula to remove the space and I still get #num!.

I tried a separate spreadsheet using the range testsort about five lines and
now I get #REF. I cant figure out what I'm doing wrong, what is the hang up?

Thanks so very much for your help. At least I know the formula is correct.
I will keep working on it. Thanks again.

"Biff" wrote:

Hi!

The formula you posted has a leading space in " Tax Billed". Is that
supposed to be there? Other than that, your formula works on my end. You can
shorten it slightly:

=IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)="TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4))

You might be able to use this much shorter alternative (normally entered,
not an array):

=SUMPRODUCT(--(INDEX(alabamasort,,1)="Tax
Billed"),--(INDEX(alabamasort,,2)="Total"),INDEX(alabamasort, ,4))

Biff

"taxmom" wrote in message
...
I have a formula that the newsgroup help me with over a year ago. It worked
perfectly and I use it though out my programs. However I want to use it
in a
similar situation and I cannot get it to work. The formula returns #num.
it
should return 723,314.70

The range name is alabamasort. It is all in 1 worksheet different tabs.
I want the formula to look in the range name alabamasort find TAX BILLED
in
column 1 and TOTAL in column 2 and return the value in column 4

The columns are as follows:
Type City/County State amount

Taxble sales cottonwood al 1,303.14
TAX BILLED TOTAL AL 723,314.70

This is the formual

=IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=" TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="
TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4))

Can you help?

#num Error index, match
 

Biff,

What does the #NUM mean. Why does this come up? I tried the second formula
and it does the same thing. The formual works outside this spreadsheet with
other data. Why not in this spreadsheet?

Please help me solve this.

"Biff" wrote:

Hi!

The formula you posted has a leading space in " Tax Billed". Is that
supposed to be there? Other than that, your formula works on my end. You can
shorten it slightly:

=IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)="TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4))

You might be able to use this much shorter alternative (normally entered,
not an array):

=SUMPRODUCT(--(INDEX(alabamasort,,1)="Tax
Billed"),--(INDEX(alabamasort,,2)="Total"),INDEX(alabamasort, ,4))

Biff

"taxmom" wrote in message
...
I have a formula that the newsgroup help me with over a year ago. It worked
perfectly and I use it though out my programs. However I want to use it
in a
similar situation and I cannot get it to work. The formula returns #num.
it
should return 723,314.70

The range name is alabamasort. It is all in 1 worksheet different tabs.
I want the formula to look in the range name alabamasort find TAX BILLED
in
column 1 and TOTAL in column 2 and return the value in column 4

The columns are as follows:
Type City/County State amount

Taxble sales cottonwood al 1,303.14
TAX BILLED TOTAL AL 723,314.70

This is the formual

=IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=" TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="
TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4))

Can you help?

#num Error index, match
 

#NUM! means that there is an invalid numeric value or the formula is
expecting a numeric value and gets something else.

Are there any other types of errors in any of the referenced ranges?

Ae you sure that leading space is actually a char(32) space? That wouldn't
cause a #NUM! error but it could lead to a result of 0.

What version of Excel are you using? See if this is available in your
version:

Select the formula cell.
Goto ToolsFormula Auditing
Trace Error

See if that shows you where the error is coming from.

Biff

"taxmom" wrote in message
...
Biff,

What does the #NUM mean. Why does this come up? I tried the second
formula
and it does the same thing. The formual works outside this spreadsheet
with
other data. Why not in this spreadsheet?

Please help me solve this.

"Biff" wrote:

Hi!

The formula you posted has a leading space in " Tax Billed". Is that
supposed to be there? Other than that, your formula works on my end. You
can
shorten it slightly:

=IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)="TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4))

You might be able to use this much shorter alternative (normally entered,
not an array):

=SUMPRODUCT(--(INDEX(alabamasort,,1)="Tax
Billed"),--(INDEX(alabamasort,,2)="Total"),INDEX(alabamasort, ,4))

Biff

"taxmom" wrote in message
...
I have a formula that the newsgroup help me with over a year ago. It
worked
perfectly and I use it though out my programs. However I want to use
it
in a
similar situation and I cannot get it to work. The formula returns
#num.
it
should return 723,314.70

The range name is alabamasort. It is all in 1 worksheet different
tabs.
I want the formula to look in the range name alabamasort find TAX
BILLED
in
column 1 and TOTAL in column 2 and return the value in column 4

The columns are as follows:
Type City/County State amount

Taxble sales cottonwood al 1,303.14
TAX BILLED TOTAL AL 723,314.70

This is the formual

=IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=" TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="
TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4))

Can you help?

#num Error index, match
 

Thank You, Thank You, Thank You !

The company just got windows PX. There was something strange. The trace
error traced to a total on the same page and a blank cell but, I had no
reference to that total either in the formula or range name. I doubled check
my ranges it is exactly the range I wanted. So, I deleted all of the
previous range names and started new and it worked. I'm very gratefull for
your help. Have a great day because you just made mine !


"Biff" wrote:

#NUM! means that there is an invalid numeric value or the formula is
expecting a numeric value and gets something else.

Are there any other types of errors in any of the referenced ranges?

Ae you sure that leading space is actually a char(32) space? That wouldn't
cause a #NUM! error but it could lead to a result of 0.

What version of Excel are you using? See if this is available in your
version:

Select the formula cell.
Goto ToolsFormula Auditing
Trace Error

See if that shows you where the error is coming from.

Biff

"taxmom" wrote in message
...
Biff,

What does the #NUM mean. Why does this come up? I tried the second
formula
and it does the same thing. The formual works outside this spreadsheet
with
other data. Why not in this spreadsheet?

Please help me solve this.

"Biff" wrote:

Hi!

The formula you posted has a leading space in " Tax Billed". Is that
supposed to be there? Other than that, your formula works on my end. You
can
shorten it slightly:

=IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)="TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4))

You might be able to use this much shorter alternative (normally entered,
not an array):

=SUMPRODUCT(--(INDEX(alabamasort,,1)="Tax
Billed"),--(INDEX(alabamasort,,2)="Total"),INDEX(alabamasort, ,4))

Biff

"taxmom" wrote in message
...
I have a formula that the newsgroup help me with over a year ago. It
worked
perfectly and I use it though out my programs. However I want to use
it
in a
similar situation and I cannot get it to work. The formula returns
#num.
it
should return 723,314.70

The range name is alabamasort. It is all in 1 worksheet different
tabs.
I want the formula to look in the range name alabamasort find TAX
BILLED
in
column 1 and TOTAL in column 2 and return the value in column 4

The columns are as follows:
Type City/County State amount

Taxble sales cottonwood al 1,303.14
TAX BILLED TOTAL AL 723,314.70

This is the formual

=IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=" TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="
TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4))

Can you help?

#num Error index, match
 

You're welcome. Thanks for the feedback!

Biff

"taxmom" wrote in message
...
Thank You, Thank You, Thank You !

The company just got windows PX. There was something strange. The trace
error traced to a total on the same page and a blank cell but, I had no
reference to that total either in the formula or range name. I doubled
check
my ranges it is exactly the range I wanted. So, I deleted all of the
previous range names and started new and it worked. I'm very gratefull
for
your help. Have a great day because you just made mine !


"Biff" wrote:

#NUM! means that there is an invalid numeric value or the formula is
expecting a numeric value and gets something else.

Are there any other types of errors in any of the referenced ranges?

Ae you sure that leading space is actually a char(32) space? That
wouldn't
cause a #NUM! error but it could lead to a result of 0.

What version of Excel are you using? See if this is available in your
version:

Select the formula cell.
Goto ToolsFormula Auditing
Trace Error

See if that shows you where the error is coming from.

Biff

"taxmom" wrote in message
...
Biff,

What does the #NUM mean. Why does this come up? I tried the second
formula
and it does the same thing. The formual works outside this spreadsheet
with
other data. Why not in this spreadsheet?

Please help me solve this.

"Biff" wrote:

Hi!

The formula you posted has a leading space in " Tax Billed". Is that
supposed to be there? Other than that, your formula works on my end.
You
can
shorten it slightly:

=IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)="TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4))

You might be able to use this much shorter alternative (normally
entered,
not an array):

=SUMPRODUCT(--(INDEX(alabamasort,,1)="Tax
Billed"),--(INDEX(alabamasort,,2)="Total"),INDEX(alabamasort, ,4))

Biff

"taxmom" wrote in message
...
I have a formula that the newsgroup help me with over a year ago. It
worked
perfectly and I use it though out my programs. However I want to
use
it
in a
similar situation and I cannot get it to work. The formula returns
#num.
it
should return 723,314.70

The range name is alabamasort. It is all in 1 worksheet different
tabs.
I want the formula to look in the range name alabamasort find TAX
BILLED
in
column 1 and TOTAL in column 2 and return the value in column 4

The columns are as follows:
Type City/County State amount

Taxble sales cottonwood al 1,303.14
TAX BILLED TOTAL AL 723,314.70

This is the formual

=IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=" TAX
BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="
TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4))

Can you help?