Structured reference to specific non-contiguous columns in array

CaraM

New Member
Joined
Apr 8, 2018
Messages
33
Office Version
  1. 365
Platform
  1. Windows
I have a named table (RMDTable) with columns that I want to add based on the date in column A (Tax Year Ending). The sum will go in a cell in a different sheet/table.

I'm currently successfully using this formula: =SUM(VLOOKUP([@[Tax Year Ending]],RMDTable[#Data],{6,7,10,11,18},FALSE))

But those columns (6,7,10,11,18) could move, and I think the spreadsheet would be better documented if I could replace those column numbers with their structured reference names.

I tried replacing the column numbers within the array within the VLOOKUP with the column names (e.g., "IRA 1 Conversion", "IRA 1 Withdrawal", "IRA 2 Conversion", "IRA 2 Withdrawal", etc.) but cannot get the formula to accept either the column name as RMDTable[IRA 1 Conversion] OR as COLUMN(RMDTable[IRA 1 Conversion]).

Is there a way to reference the column by its name, rather than its position number, in the formula that am currently successfully using?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
You could hard-code your column names into a formula ....

but in terms of better documenting the spreadsheet, and allowing easier edits, perhaps something like:

ABCDEFGHIJKLM
1
2SubTotal1SubTotal2
3IRA 1 Conversionxxx
4IRA 1 Withdrawalyyy
5IRA 2 Conversion
6IRA 2 Withdrawal
7
8Tax Year Ending2xxx4yyyIRA 1 ConversionIRA 1 Withdrawal89IRA 2 ConversionIRA 2 Withdrawal12
9201922441357
102020335511131719
1120211234
12
13
14Tax Year EndingSubTotal1SubTotal2
1520191666
1620206088
Sheet1
Cell Formulas
RangeFormula
C3C3=RMDTable[[#Headers],[xxx]]
C4C4=RMDTable[[#Headers],[yyy]]
B3B3=RMDTable[[#Headers],[IRA 1 Conversion]]
B4B4=RMDTable[[#Headers],[IRA 1 Withdrawal]]
B5B5=RMDTable[[#Headers],[IRA 2 Conversion]]
B6B6=RMDTable[[#Headers],[IRA 2 Withdrawal]]
C15:C16C15=SUM(VLOOKUP([@[Tax Year Ending]],RMDTable,MATCH(SubTotal1,RMDTable[#Headers],),))
D15:D16D15=SUM(VLOOKUP([@[Tax Year Ending]],RMDTable,MATCH(SubTotal2,RMDTable[#Headers],),))
Named Ranges
NameRefers ToCells
SubTotal1=Sheet1!$B$3:$B$6C15:C16
SubTotal2=Sheet1!$C$3:$C$4D15:D16
 
Upvote 0
Solution

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top