Forum Discussion
Bringing Multiple Columns Over from VLOOKUP Table
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).
| Department | Charges | Receipts |
| CATEGORY 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 DEPT | Sum of Charges | Sum of Payments |
| EP OFFICE | $116,499.00 | $33,516.18 |
| MISCELLANEOUS | $- | $22.00 |
| NP OFFICE | $6,776.00 | $1,785.11 |
| PQRS | $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
2 Replies
=IFERROR(VLOOKUP($A2,$F$17:$I$24,MATCH(TRUE,ISNUMBER(SEARCH(B$1,$G$16:$I$16)),0)+1,FALSE),"")
Does this formula return the intended result in the sample file? The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021. Once the size of the Pivot Table changes the ranges $F$17:$I$24 and $G$16:$I$16 of the formula must be adapted.
Using an INDEX/MATCH formulation would be ideal when coping with the fact of not entering the index number manually. The thing is that I recommend that you use the same headers in the first table as in the PivotTable, unless you want to use another workaround.
See attached document with proposed solution. Enter the formula on B2 and you can then just drag it down and right.
