Excel question: VLOOKUP what's already been VLOOKUPed?

April 14, 2014 12:28 PM

Is there a way to look up items in Excel that are not included in a total that has been calculated using multiple vlookups?

I created a spreadsheeet that automates budget reporting for an organization I work for. Because the budget line items do not exactly match the accounts in our accounting software, I used to have to match up accounts with budget line items, add them together, and manually enter the totals into the budget report in Excel. Obviously this was a slow process, so I automated it using VLOOKUP. I export a CSV from the accounting software, paste it into sheet 1, and then everything is filled in on sheet 2.

The problem I would like to solve is this: often there are items that were not included in the original layout of the budget report. So, for example, for travel on the budget I might have =(vlookup "travel - hotels", etc., etc.)+(vlookup "travel - mileage", etc., etc.), but say account "travel - rental cars" has had some expenses added since I created the original, and it should go into the travel line on the budget as well.

I have a total at the bottom so I can tell if the balance matches what the accounting software has. It is pretty simple to figure out which two or three accounts need to be added in and spend a few minutes fixing the report, but I would really like this to be as automatic as possible.

Is there a way to look up and identify those accounts in sheet 1 that have not been added into the report on sheet 2? Of course it wouldn't do me any good unless it also identified them visually in some way, so suggestions for that would be appreciated.

Thank you!

posted by natteringnabob to Technology (7 answers total)

Run your formula, and anything that doesn't match shouuld come up with #N/A error. You can then apply conditional formatting (assuming you do a copy and paste special values at some point,) and then have those cells with the #N/A error highlight in yellow.
posted by Ruthless Bunny at 12:32 PM on April 14, 2014 [1 favorite]

you could also just pick up "travel" as a category if you wanted to.

That would mean you could have your specific vlookups, you'd generate a total (use sumif for this) and then you create a plug for the unassigned travel.
posted by JPD at 12:40 PM on April 14, 2014

Response by poster: Thanks, Ruthless Bunny - but wouldn't that only work if I included every possible account into the vlookup formulas? My problem is that it's not included in my lookup formula, and I don't know to include it until I see that the balances are off, which alerts me to the fact that there's an account in sheet 1 that isn't included in the vlookups in sheet 2.
posted by natteringnabob at 12:40 PM on April 14, 2014

Are the account categories organized in a column on the second sheet? If not, do that, and then create a vlookup that looks up the budget category values in the budget line items on the first sheet in your list of categories. Basically reverse the direction of your vlookup. If you use 1 for the column number in the vlookup, it'll just returns the value that it's looking up, or returns an error. If you want to get fancy you can use an if statement with the ISERROR function and return "on the list"/"not on the list" or something like that.
posted by yarrow at 12:52 PM on April 14, 2014 [1 favorite]

You could do it in reverse.

If your categories are exactly the same in the spreadsheet. Once you've imported the latest info into the feeder spreadsheet, insert a column on column B this is where the temporary VLOOKUP formula will go.

On the formula spreadsheet, give the Categories column a Named Range of Categories (or whatever.)

Do the formula VLOOKUP(A2,Categories,1,false)

Then copy and paste special values.

Then do your conditional highlighting.

Bob's your uncle, there you are.

I know 7000 jankity ways to get what I need out of Excel. I could rule the world if I knew VBA.
posted by Ruthless Bunny at 12:52 PM on April 14, 2014 [2 favorites]

Have you tried doing this with Pivot tables instead of VLOOKUPs?

You'd set the source as your Sheet 1, put the Pivot on Sheet 2.

Then you can group items to get your subtotals.

Each month when you add to/replace the data in Sheet 1, you'll refresh the Pivot and any new items will be in a blank group at the bottom, waiting for you to add them into the group they belong to.
posted by chrispy108 at 12:55 PM on April 14, 2014 [2 favorites]

If you're trying to sum up everything that begins with travel, then you want to do this =SUMIFS(ValuesToAdd,ExpenseCodes,"Travel*").
posted by ambrosen at 2:36 PM on April 14, 2014