Greetings Colleagues! We are Becca Sanders (Hood River, Oregon) and Heather Robinson (Rochester, New York) of Iteration Evaluation LLC. Our forte is the “…ahhh… there is no button on your system for that” analysis requests. We work mostly with behavioral health organizations whose current informational needs often far exceed what existing systems can pull off.
Enter VLOOKUP. VLOOKUP is a tool in Excel that can be used to amalgamate data data that lives on seperate spreadsheets. It’s like a 4 wheel drive vehicle that can get stakeholders what they need across the potholes that result from siloed management information systems. For example, say we have a direct service provider with a caseload of folks he or she is trying to help. We also have spreadsheet X, that lives in one place, and gives the provider a list of something important—say emergency room visits. And we have spreadsheet Y that lives elsewhere and contains some other important piece of information about individuals the provider is trying to help—say existence of a chronic condition. The provider wants this information all in one place across a particular group of individuals—say a caseload– in order to do their job well. Enter VLOOKUP as the solution; a solution while you wait for relational table builds, system integration, or the like. One common identifier across different spreadsheets (for example, a person’s ID number) PLUS a bazillion exported spreadsheets, none of which quite do the trick, PLUS VLOOKUP is all you need. VLOOKUP essentially says “hey, here is an ID number for an individual in this table and that one (and that one and that one ad nauseum). Let’s use it to pull in all values from one spreadsheet to the other (and so on) until all the information you need is in one place!” In that way, VLOOKUP allows you to deal with situations where the snapshot you are trying to take involves variables that live in different systems – especially systems that don’t speak to one another very well. Get good at it! It will take you far! It is like a Subaru in the winter which can get you through just about any wacky data set up you are faced with.
Rad Resource: Need more of a leg up to get started? Check out the free resource www.chandoo.org! Chandoo has a free blog, with a mission to help anyone become awesome in EXCEL.
Go VLOOKUP! Don’t wait to kick your car into VLOOKUP 4 Wheel Drive!
Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org . aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.