#num Error index, match

[Guest]

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(alabamasort,,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?

 

[Biff]

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,INDEX(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

 

[Guest]

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.

 

[Guest]

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]

#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 Tools>Formula Auditing
Trace Error

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

Biff

 

[Guest]

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]

You're welcome. Thanks for the feedback!

Biff

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 !