array formula

H

HeatherTaxONA

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!!
 
T

Toppers

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top