Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
can an array formula do this? | Excel Worksheet Functions | |||
Array formula | Excel Discussion (Misc queries) | |||
Array formula | Excel Discussion (Misc queries) | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions |