Excel wizardry needed
April 16, 2010 9:30 AM Subscribe
MS Excel-filter: I want to be able to search for the second occurrence of a word in column A and then pull the corresponding value from column B. More inside.
I have an operating expense statement for a building and I want a formula that can pull year one real estate taxes. This would be pretty easy if real estate taxes only appeared one on the operating expense statement. However, many leases are structured to reimburse landlords for real estate taxes, and so the operating expense statement will have a section for reimbursed expenses before the operating expense statement.
Generally, it will look like this:
Rental Revenue
....
Expense Reimbursement Revenue
Real Estate Taxes
...
Operating Expenses
Real Estate Taxes
...
My brilliant idea had been to use match() to find where operating expenses started, and then to get a cell location using address() and then pipe this into offset() to get a range for the entire operating expenses section by just using a height of an arbitrarily large enough length, and then putting this into a vlookup.
But for some reason offset() wasn't able to handle the reference provided by address and so nothing is working.
My questions are:
Why is offset() not accepting the input from address()?
And is my approach overly complicated? What's a better way to do this?
I have an operating expense statement for a building and I want a formula that can pull year one real estate taxes. This would be pretty easy if real estate taxes only appeared one on the operating expense statement. However, many leases are structured to reimburse landlords for real estate taxes, and so the operating expense statement will have a section for reimbursed expenses before the operating expense statement.
Generally, it will look like this:
Rental Revenue
....
Expense Reimbursement Revenue
Real Estate Taxes
...
Operating Expenses
Real Estate Taxes
...
My brilliant idea had been to use match() to find where operating expenses started, and then to get a cell location using address() and then pipe this into offset() to get a range for the entire operating expenses section by just using a height of an arbitrarily large enough length, and then putting this into a vlookup.
But for some reason offset() wasn't able to handle the reference provided by address and so nothing is working.
My questions are:
Why is offset() not accepting the input from address()?
And is my approach overly complicated? What's a better way to do this?
Not sure about the second occurence without using VBA, but you can use VLOOKUP() to get the corresponding column.
posted by a womble is an active kind of sloth at 10:05 AM on April 16, 2010
posted by a womble is an active kind of sloth at 10:05 AM on April 16, 2010
How about simply this formula in C2, then dragged downward:
=IF(COUNTIF(A$2:A2,A2)=2,B2,)
posted by Oddly at 10:17 AM on April 16, 2010
=IF(COUNTIF(A$2:A2,A2)=2,B2,)
posted by Oddly at 10:17 AM on April 16, 2010
...or possibly:
=IF(COUNTIF(A$2:A2,"Real Estate Taxes")=2,B2,)
posted by Oddly at 10:23 AM on April 16, 2010
=IF(COUNTIF(A$2:A2,"Real Estate Taxes")=2,B2,)
posted by Oddly at 10:23 AM on April 16, 2010
I think Oddly is on to something, namely that you should find an independent way to indicate that a given cell is indeed the second occurrence of the word in the column. Then, you can use vlookup to gt the value from column B.
posted by soelo at 10:54 AM on April 16, 2010
posted by soelo at 10:54 AM on April 16, 2010
Response by poster: Thanks for the responses so far. I'm really looking for something that doesn't require me to devote an entire column to facilitate this.
Any idea why my approach mentioned above doesn't work?
posted by prunes at 11:48 AM on April 16, 2010
Any idea why my approach mentioned above doesn't work?
posted by prunes at 11:48 AM on April 16, 2010
This seems to work nicely:
=INDEX(A1:B5,SMALL(IF(A1:A5="Real Estate Taxes",ROW(A1:A5)-CELL("row",A1:A5)+1,999999999),2),2)
It's an array formula so you need to press Ctrl+Shift+Enter after typing it into the formula bar.
posted by Well that's a lie at 12:32 PM on April 16, 2010
=INDEX(A1:B5,SMALL(IF(A1:A5="Real Estate Taxes",ROW(A1:A5)-CELL("row",A1:A5)+1,999999999),2),2)
It's an array formula so you need to press Ctrl+Shift+Enter after typing it into the formula bar.
posted by Well that's a lie at 12:32 PM on April 16, 2010
...and just for fun it using your VLOOKUP/MATCH combo:
=VLOOKUP(A4,INDIRECT(ADDRESS(MATCH("Operating Expenses",A1:A5,0)+1,1)&":B999"),2,FALSE)
posted by Well that's a lie at 12:43 PM on April 16, 2010
=VLOOKUP(A4,INDIRECT(ADDRESS(MATCH("Operating Expenses",A1:A5,0)+1,1)&":B999"),2,FALSE)
posted by Well that's a lie at 12:43 PM on April 16, 2010
Whoops...
=VLOOKUP("Real Estate Taxes",INDIRECT(ADDRESS(MATCH("Operating Expenses",A1:A5,0)+1,1)&":B999"),2,FALSE)
posted by Well that's a lie at 12:47 PM on April 16, 2010
=VLOOKUP("Real Estate Taxes",INDIRECT(ADDRESS(MATCH("Operating Expenses",A1:A5,0)+1,1)&":B999"),2,FALSE)
posted by Well that's a lie at 12:47 PM on April 16, 2010
Response by poster: Well that's a lie, can you explain your formula? I don't understand the purpose of the -CELL("row",A1:A5)+1, and the formula seems to pull OK with it removed.
Also, one other nitpicky thing: I'd like for this formula to be robust enough where it would still work for those properties that do not have real estate taxes reimbursed. In this scenario there would only be one line item for real estate taxes, and your use of small() wouldn't work anymore. Any suggestions on how to make the formula robust enough to handle this circumstance?
posted by prunes at 1:36 PM on April 16, 2010
Also, one other nitpicky thing: I'd like for this formula to be robust enough where it would still work for those properties that do not have real estate taxes reimbursed. In this scenario there would only be one line item for real estate taxes, and your use of small() wouldn't work anymore. Any suggestions on how to make the formula robust enough to handle this circumstance?
posted by prunes at 1:36 PM on April 16, 2010
Response by poster: I'm trying to modify your vlookup formula for my purposes and am running into some trouble. I'll actually be linking across workbooks and don't have much experience with using address(). What am I doing wrong here? My formula REFs out:
=VLOOKUP("Real Estate Taxes",INDIRECT(ADDRESS(MATCH("Operating Expenses",'[Builder''s Leasing Bldg.xls]Cash Flow'!$A$1:$A$100,0)+1,1,,,"'[Builder''s Leasing Bldg.xls]Cash Flow'")&":$C$999"),3,FALSE)
posted by prunes at 2:00 PM on April 16, 2010
=VLOOKUP("Real Estate Taxes",INDIRECT(ADDRESS(MATCH("Operating Expenses",'[Builder''s Leasing Bldg.xls]Cash Flow'!$A$1:$A$100,0)+1,1,,,"'[Builder''s Leasing Bldg.xls]Cash Flow'")&":$C$999"),3,FALSE)
posted by prunes at 2:00 PM on April 16, 2010
INDIRECT can't work with closed workbooks, which is why you're getting #REF! There are some workarounds available, however the array formula can be easily modified to work where there is only one 'Real Estate Taxes' line item.
=INDEX([Book2.xlsx]Sheet1!$A$1:$B$5,SMALL(IF([Book2.xlsx]Sheet1!$A$1:$A$5="Real Estate Taxes",ROW([Book2.xlsx]Sheet1!$A$1:$A$5)-ROW([Book2.xlsx]Sheet1!$A$1)+1,999999999),MIN(2,SUM(IF([Book2.xlsx]Sheet1!$A$1:$A$5="Real Estate Taxes",1)))),2)
We needed the -CELL("row",A1:A5)+1 because the ROW(A1:A5) in the formula tells us the row number where "Real Estate Taxes" appears however INDEX function expects an offset. So if instead of A1:A5 our range was A3:B8 and our magic phrase was in A7, ROW would give us 7, but we would need to supply 7 - 3 + 1 = 5 to the INDEX function because A7 is in the fifth row of the range A3:B8. Of course if you're starting from cell A1 this part is unnecessary.
(The CELL has been changed to use ROW in this second formula as the CELL function does not work with closed workbooks).
posted by Well that's a lie at 5:08 PM on April 16, 2010
=INDEX([Book2.xlsx]Sheet1!$A$1:$B$5,SMALL(IF([Book2.xlsx]Sheet1!$A$1:$A$5="Real Estate Taxes",ROW([Book2.xlsx]Sheet1!$A$1:$A$5)-ROW([Book2.xlsx]Sheet1!$A$1)+1,999999999),MIN(2,SUM(IF([Book2.xlsx]Sheet1!$A$1:$A$5="Real Estate Taxes",1)))),2)
We needed the -CELL("row",A1:A5)+1 because the ROW(A1:A5) in the formula tells us the row number where "Real Estate Taxes" appears however INDEX function expects an offset. So if instead of A1:A5 our range was A3:B8 and our magic phrase was in A7, ROW would give us 7, but we would need to supply 7 - 3 + 1 = 5 to the INDEX function because A7 is in the fifth row of the range A3:B8. Of course if you're starting from cell A1 this part is unnecessary.
(The CELL has been changed to use ROW in this second formula as the CELL function does not work with closed workbooks).
posted by Well that's a lie at 5:08 PM on April 16, 2010
This thread is closed to new comments.
i'll come back later
posted by jckll at 9:55 AM on April 16, 2010