Excel question: VLOOKUP what's already been VLOOKUPed?
April 14, 2014 12:28 PM Subscribe
Is there a way to look up items in Excel that are not included in a total that has been calculated using multiple vlookups?
posted by natteringnabob to Technology (7 answers total) 2 users marked this as a favorite
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.