Howdy folks,

Looking for advice on a VLOOKUP task and how you might handle it.

In this example, I have a template that has fixed values in the rows (Department names), and two columns (Charges and Receipts).

DepartmentChargesReceiptsCATEGORY III CODES  EP OFFICE  PREVENTATIVE  JOINT INJECTION  LESION/ABSCESS  MEDICAL  MISCELLANEOUS  NP OFFICE  NP PREVENTIVE  PQRS  PROCEDURES  

I also have a pivot table that is formatted in the same way, but does not always have values for each Department row.

USE DEPTSum of ChargesSum of PaymentsEP OFFICE $116,499.00 $33,516.18MISCELLANEOUS $-   $22.00NP OFFICE $6,776.00 $1,785.11PQRS $0.43 $0.01

Normally, what I would do, is in the first Charges cell on the empty table (B2) I would do a formula combining an iferror with a vlookup, reference the Department name in Column A on the first table with Department name in column A of my pivot table, to pull over the Charges in the second column.  Then I would have to type almost the same formula again under the Receipts column, but pull from column 3.

I’m doing much larger tables than just this example set.  Is there a way to quickly either drag the iferror(vlookup) formula, or copy and paste it, across multiple columns on the empty table, while changing the column number that it pulls from the pivot table to correspond?

I can lock in the lookup value and the table array values using $ to prevent them from shifting as the formula is copied or dragged across, but the index number always remains the same when doing that.  In the example above, if I put my formula in the empty Charges column, used $ to lock in the lookup value to that column, selected my entire pivot table, and indexed on column two (charges), then copied that formula over to the next column (receipts) it would still pull the charges from the pivot. 

I want to be able to copy/drag that vlookup across 50+ columns and hundreds of rows, without having to then manually change the index number in row one in every single column to match the column from the array, before then copying/dragging that down each subsequent column.

Clear?  Confusing?

Thoughts?

Thanks in advance.

Travis

​Howdy folks,Looking for advice on a VLOOKUP task and how you might handle it. In this example, I have a template that has fixed values in the rows (Department names), and two columns (Charges and Receipts).DepartmentChargesReceiptsCATEGORY III CODES  EP OFFICE  PREVENTATIVE  JOINT INJECTION  LESION/ABSCESS  MEDICAL  MISCELLANEOUS  NP OFFICE  NP PREVENTIVE  PQRS  PROCEDURES   I also have a pivot table that is formatted in the same way, but does not always have values for each Department row.USE DEPTSum of ChargesSum of PaymentsEP OFFICE $116,499.00 $33,516.18MISCELLANEOUS $-   $22.00NP OFFICE $6,776.00 $1,785.11PQRS $0.43 $0.01 Normally, what I would do, is in the first Charges cell on the empty table (B2) I would do a formula combining an iferror with a vlookup, reference the Department name in Column A on the first table with Department name in column A of my pivot table, to pull over the Charges in the second column.  Then I would have to type almost the same formula again under the Receipts column, but pull from column 3.I’m doing much larger tables than just this example set.  Is there a way to quickly either drag the iferror(vlookup) formula, or copy and paste it, across multiple columns on the empty table, while changing the column number that it pulls from the pivot table to correspond? I can lock in the lookup value and the table array values using $ to prevent them from shifting as the formula is copied or dragged across, but the index number always remains the same when doing that.  In the example above, if I put my formula in the empty Charges column, used $ to lock in the lookup value to that column, selected my entire pivot table, and indexed on column two (charges), then copied that formula over to the next column (receipts) it would still pull the charges from the pivot.  I want to be able to copy/drag that vlookup across 50+ columns and hundreds of rows, without having to then manually change the index number in row one in every single column to match the column from the array, before then copying/dragging that down each subsequent column. Clear?  Confusing? Thoughts? Thanks in advance. Travis  Read More

​