# Excel Question - Using Offset and Match with only visible columns **URL:** \ **Category:** Factual Questions\ **Created:** [January 15, 2024, 4:21pm UTC](https://boards.straightdope.com/t/excel-question-using-offset-and-match-with-only-visible-columns/996122 "2024-01-15T16:21:52Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Zakalwe](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zakalwe/32/270_2.png) [@Zakalwe](https://boards.straightdope.com/u/Zakalwe)\ **Post date:** [January 15, 2024, 4:21pm UTC](https://boards.straightdope.com/t/excel-question-using-offset-and-match-with-only-visible-columns/996122/1 "2024-01-15T16:21:52Z")
Okay, so I have a spreadsheet where Column A always has a value. Cells B through N may have values (or they may not - B and F might have values, but C D E would not). And this can different for every row in the table. Next row down might have values in A D I M. So the first row is F minus B and the second row is M minus I. I have a very complex formula in column O that finds the last column that has a value and finds the difference between it and the most ‘recent’ prior cell that has a value like thus: =IF(N1\<\>“”,N1-OFFSET(A1,0,MATCH(MAX(A1:M1)+1,A1:M1,1)-1), IF(M1\<\>“”,M1-OFFSET(A1,0,MATCH(MAX(A1:L1)+1,A1:L1,1)-1), IF(L1\<\>“”,L1-OFFSET(A1,0,MATCH(MAX(A1:K1)+1,A1:K1,1)-1), IF(K1\<\>“”,K1-OFFSET(A1,0,MATCH(MAX(A1:J1)+1,A1:J1,1)-1), IF(J1\<\>“”,J1-OFFSET(A1,0,MATCH(MAX(A1:I1)+1,A1:I1,1)-1), IF(I1\<\>“”,I1-OFFSET(A1,0,MATCH(MAX(A1:H1)+1,A1:H1,1)-1), IF(H1\<\>“”,H1-OFFSET(A1,0,MATCH(MAX(A1:G1)+1,A1:G1,1)-1), IF(G1\<\>“”,G1-OFFSET(A1,0,MATCH(MAX(A1:F1)+1,A1:F1,1)-1), IF(F1\<\>“”,F1-OFFSET(A1,0,MATCH(MAX(A1:E1)+1,A1:E1,1)-1), IF(E1\<\>“”,E1-OFFSET(A1,0,MATCH(MAX(A1:D1)+1,A1:D1,1)-1), IF(D1\<\>“”,D1-OFFSET(A1,0,MATCH(MAX(A1:C1)+1,A1:C1,1)-1), IF(C1\<\>“”,C1-OFFSET(A1,0,MATCH(MAX(A1:B1)+1,A1:B1,1)-1), IF(B1\<\>“”,B1-A1,0))))))))))))) And this works just fine as is (if a little clunky). What I need though, is for the above to work exactly as it does now, but only on _visible_ columns, so that if I hide column I, the second row above would become M minus D. I really want to avoid VBA since this is a large data set and VBA functions really cripple the sheet. Any help out there? ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [January 15, 2024, 7:07pm UTC](https://boards.straightdope.com/t/excel-question-using-offset-and-match-with-only-visible-columns/996122/2 "2024-01-15T19:07:54Z")
It’s hard to get the visibility status of a column without VBA. Instead, can you use a simple cell to indicate whether the column should be included in the calculations? Then you can simplify the rest of the formula, I think. Here’s a working [online example](https://cocc-my.sharepoint.com/:x:/g/personal/rtuan_cocc_edu/EXwe8GSAACRFlbypxa9bMi0BKjpQy9EtL8Tppwlhu0QTyg?e=ogJYg8) or [just a screenshot](https://share.cleanshot.com/qYR6qXxz). To break it down: - A simple `TRUE/FALSE` cell in row 3 indicates whether that column should be included in calculations. Instead of using show/hide columns, you can use conditional formatting to indicate whether that column is “hidden”. - We can use [`=FILTER(B5:G5, (B5:G5<>"")`](https://support.microsoft.com/en-au/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759) to get all the non-blank cells - We combine that with ` * (B$3:G$3<>TRUE)` to exclude the “hidden” columns from row 3. (The `*` operator combines filters, and the whole filter formula becomes `=FILTER(B5:G5, (B5:G5<>"") * (B$3:G$3<>TRUE))`) - Then we use [`=TAKE(data, 1, -2)`](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003) to get the last two values of that filtered output. That’s what the `1, -2` parameters do (first row, last two values starting from the end). - We save that `=TAKE()` output into a variable called `LastTwoNonEmptyNotHiddenValues`. - We subtract the second-to-last `LastTwoNonEmptyNotHiddenValues` from the last `LastTwoNonEmptyNotHiddenValues` using `INDEX()`. ---
**Author:** ![Zakalwe](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zakalwe/32/270_2.png) [@Zakalwe](https://boards.straightdope.com/u/Zakalwe)\ **Post date:** [January 15, 2024, 8:34pm UTC](https://boards.straightdope.com/t/excel-question-using-offset-and-match-with-only-visible-columns/996122/3 "2024-01-15T20:34:04Z")
With a few modifications that worked perfectly! The issue that the columns actually ARE hidden and it’s done by VBA, I just had to add code to modify the column flag during the show/hide process and modify the formula to deal with situations where either only one column had a value or only one column was showing. Thanks tons! ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [January 15, 2024, 8:40pm UTC](https://boards.straightdope.com/t/excel-question-using-offset-and-match-with-only-visible-columns/996122/4 "2024-01-15T20:40:00Z")
> [@Zakalwe](#): > > With a few modifications that worked perfectly! > […] > Thanks tons! Great! And welcome 🙂 ---
**Author:** ![Zakalwe](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zakalwe/32/270_2.png) [@Zakalwe](https://boards.straightdope.com/u/Zakalwe)\ **Post date:** [January 15, 2024, 8:51pm UTC](https://boards.straightdope.com/t/excel-question-using-offset-and-match-with-only-visible-columns/996122/5 "2024-01-15T20:51:15Z")
TAKE is something I’m definitely going to have to look into more.