array formula
 

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(alabamas ort,,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!!

array formula
 

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")*(D2:D4="GROSS
SALES"),0),5)),0,INDEX(alabamasort,MATCH(1,(A2:A4= "CITY TOTALS-----:
PRATTVILLE")*(B2:B4="AUTAUGA")*(D2:D4="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


"HeatherTaxONA" wrote:

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(alabamas ort,,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!!