Queries in Excel
Monday 21 May 2007 - Filed under Default
If you’re a spreadsheet junky like me, you’ve learned all kinds of things to do in a spreadsheet. From using $’s in autofills to cross-workbook cell references to pivot tables. There’s lots of fun to be had. I’ve been using Excel and OpenOffice Spreadsheet for years and was pleasantly surprised to find this function the other day: vlookup. It was used to reconcile one statement against another and I think it’s a good one to remember.
If you ever wanted to create a form to query your huge spreadsheet without having to go over to Access or some other software, you should take a look at vlookup. Here’s an example:
VLOOKUP(D1;A3:B20;2)
What this does to use the value D1 to search the range A3:B20 for a value. This could be like an account or invoice number. Then it returns the value in the second column(given by the 2 as the last argument) as the value of VLOOKUP. This value could be the dollar amount of # of widgets on the invoice.
If you’d like to see it in action, click here to download an example spreadsheet. Enjoy your new querying powers!
2007-05-21 » David Sterry