Table column references help please!

New Contributor



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:




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.



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



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