Table column references help please!

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