array formula

[Guest]

I have 6 columns on a spreadsheet: city county, county, state, type, gross,
and tax. It is set up in that order. I need to pull the information
specifically from the gross and tax columns. I am using this formula now:
=IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="CITY TOTALS-----:
PRATTVILLE")*(INDEX(alabamasort,,2)="COUNTY TOTALS---:
AUTAUGA")*(INDEX(alabamasort,,4)="GROSS
SALES"),0),5)),0,INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="CITY
TOTALS-----: PRATTVILLE")*(INDEX(alabamasort,,2)="COUNTY TOTALS---:
AUTAUGA")*(INDEX(alabamasort,,4)="GROSS SALES"),0),5))

This is modified from something my manager had received from you guys. This
issue is that its not pulling the data from where I need it. Here is an
example of the columns from the formula above. (prattville) I will put a star
in between columns

CITY TOTALS-----: PRATTVILLE*AUTAUGA*.....ALABAMA*Gross Sales*6451.18*0
CITY TOTALS-----: PRATTVILLE*AUTAUGA*.....ALABAMA*Taxable Amt*6451.18*0
CITY TOTALS-----: PRATTVILLE*AUTAUGA*.....ALABAMA*Tax Amt*258.05*387.08

In this particular formula, I would need the first 6451.18. Also, these are
the first 3 lines of the excel document. Can you help??? Thanks so much!!

 

[Guest]

This formula gives the result, assuming data is in columns A to F and
"alabamasort" is defined as A-F range:

=IF(ISNA(INDEX(alabamasort,MATCH(1,(A2:A4="CITY TOTALS-----:
PRATTVILLE")*(B2:B4="AUTAUGA")*(D24="GROSS
SALES"),0),5)),0,INDEX(alabamasort,MATCH(1,(A2:A4="CITY TOTALS-----:
PRATTVILLE")*(B2:B4="AUTAUGA")*(D24="GROSS SALES"),0),5))

Note that original formula has COUNTY TOTALS---: AUTAUGA" whereas your data
only had "AUTAUGA".

INDEX(alabamasort ...) does not work as defined as it needs to account for
rows and columns, rather just columns as your formula.

HTH