Table column references help please!

%3CLINGO-SUB%20id%3D%22lingo-sub-2861376%22%20slang%3D%22en-US%22%3ETable%20column%20references%20help%20please!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2861376%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20created%20a%20macro%20setup%20that%20takes%20raw%20data%20from%20a%20remittance%20(1st%20tab)%2C%20pastes%20it%20in%20a%20table%20(2nd%20tab)%2C%20and%20the%203rd%20tab%20uses%20formulas%20to%20reference%20the%20table%20name%20%26amp%3B%20headers%20to%20do%20a%20reconciliation%2C%20partially%20manual%20(which%20is%20why%20I%20don't%20use%20Pivot).%26nbsp%3B%20It%20works%20great%2C%20except%20the%20main%20issue%20I%20have%20is%20that%20the%20remittance%20data%20is%20inconsistent%20in%20which%20column%20the%20main%20reference%20(Invoice%20%23)%20comes%20from.%26nbsp%3B%20In%20one%20remit%2C%20the%20column%20would%20be%20Col%20U%2C%20in%20another%20it%20would%20be%20Col%20T.%26nbsp%3B%20Therefore%2C%20I%20need%20my%20column%20reference%20to%20be%20flexible%20based%20on%20the%20header%20name%2C%20which%20is%20consistent.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20use%20the%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(RawData%5B%23Headers%5D%2C%2CMIN(IF(RawData%3DReconciliation!A7%2CCOLUMN(RawData%5B%23All%5D))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eto%20Index%20the%20column%20name%20I%20need.%26nbsp%3B%20A7%20is%20the%20invoice%20%23%20that%20acts%20as%20the%20anchor%20to%20lookup.%26nbsp%3B%20When%20I%20plug%20that%20into%20my%20main%20formula%20in%20Cell%20B6%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(RawData%2CMATCH(%24A6%2CRawData%5B%5B%3CU%3E%3CSTRONG%3EPayer%20Account%20'%23%5D%3A%5BPayer%20Account%20'%23%3C%2FSTRONG%3E%3C%2FU%3E%5D%5D%2C0)%2CMATCH(B%245%2CRawData%5B%23Headers%5D%2C0))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20place%20of%20the%20Payer%20Account%20%23%2C%20which%20the%20column%20reference%20that%20moves%2C%20Excel%20won't%20let%20me%20leave%20the%20formula%20bar%20since%20I%20can't%20figure%20out%20how%20to%20insert%20a%20formula%20for%20a%20column%20reference%20in%20a%20table.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20'Payer%20Account%20%23'%20started%20as%20'Invoice%20%23'%2C%20but%20when%20I%20inserted%20a%20different%20remit%20that%20didn't%20have%20the%20same%20column%20setup%20(there%20was%20one%20less)%2C%20my%20column%20shifted%20to%20'Payer%20Account%20%23'%2C%20and%20now%20none%20of%20the%20other%20references%20in%20the%20table%20work.%26nbsp%3B%20The%20first%20image%20is%20the%20current%20result.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MattPfeifer_2-1634656067208.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318410i11C965ABA3D10D38%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MattPfeifer_2-1634656067208.png%22%20alt%3D%22MattPfeifer_2-1634656067208.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20second%20image%20is%20the%20correct%20result%20when%20the%20'Payer%20Account%20%23'%20reference%20is%20'Invoice%20%23'%20like%20it%20should%20be.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MattPfeifer_1-1634656025483.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318409i88891F6E18B16F1F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MattPfeifer_1-1634656025483.png%22%20alt%3D%22MattPfeifer_1-1634656025483.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20how%20to%20either%20merge%20the%20first%20formula%20I%20pasted%20(to%20find%20the%20column%20header)%20with%20the%20second%20formula%20to%20make%20the%20search%20dynamic%2C%20or%20lock%20the%20header%20name%20so%20I'm%20still%20referencing%20the%20'Invoice%20%23'%20column%20regardless%20of%20which%20column%20letter%20it's%20in%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2861376%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2861388%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20column%20references%20help%20please!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2861388%22%20slang%3D%22en-US%22%3EPS%20Ignore%20the%20difference%20in%20Invoice%20%23%20between%20the%20images%2C%20they%20are%20two%20separate%20remits%20for%20reference%20only%3C%2FLINGO-BODY%3E
New Contributor

Hi

 

I created a macro setup that takes raw data from a remittance (1st tab), pastes it in a table (2nd tab), and the 3rd tab uses formulas to reference the table name & headers to do a reconciliation, partially manual (which is why I don't use Pivot).  It works great, except the main issue I have is that the remittance data is inconsistent in which column the main reference (Invoice #) comes from.  In one remit, the column would be Col U, in another it would be Col T.  Therefore, I need my column reference to be flexible based on the header name, which is consistent.  

 

I can use the formula:

 

=INDEX(RawData[#Headers],,MIN(IF(RawData=Reconciliation!A7,COLUMN(RawData[#All]))))

 

to Index the column name I need.  A7 is the invoice # that acts as the anchor to lookup.  When I plug that into my main formula in Cell B6:

 

=IFERROR(INDEX(RawData,MATCH($A6,RawData[[Payer Account '#]:[Payer Account '#]],0),MATCH(B$5,RawData[#Headers],0)),"")

 

in place of the Payer Account #, which the column reference that moves, Excel won't let me leave the formula bar since I can't figure out how to insert a formula for a column reference in a table. 

 

The 'Payer Account #' started as 'Invoice #', but when I inserted a different remit that didn't have the same column setup (there was one less), my column shifted to 'Payer Account #', and now none of the other references in the table work.  The first image is the current result.

MattPfeifer_2-1634656067208.png

 

The second image is the correct result when the 'Payer Account #' reference is 'Invoice #' like it should be.

MattPfeifer_1-1634656025483.png

 

Does anyone know how to either merge the first formula I pasted (to find the column header) with the second formula to make the search dynamic, or lock the header name so I'm still referencing the 'Invoice #' column regardless of which column letter it's in?

 

Thanks in advance!

1 Reply
PS Ignore the difference in Invoice # between the images, they are two separate remits for reference only