Yes please. I use spreadsheets a ton but am lost when it comes to VLookup, pivot tables, linking to different sheets. Will be watching to see where this goes.
From a business standpoint I could see spending a few dollars to learn more about spreadsheets. Yes, these resources are available in other places (there may even be an organized set of Spreadsheet tutorials out there) but I didn't see that on HN this morning.
If it helps, try to think about VLOOKUP as the same method you used to use when looking up information in a phone book. To start, all you know is the name, and you want to lookup the phone number associated with that name.
There are four parts to a VLOOKUP formula...
1. The cell that contains the information you know (e.g. someone's name).
2. The grid of cells that contains the information you want to match against and the information you want to return (e.g. a table with names of people and their phone numbers). The first row of this grid should always be the list you want to match against, and the things you want to return should always be to the right of this (e.g. if the column order is 'Names, PhoneNumber' the Vlookup can work, if it's 'PhoneNumber,Names' then it won't, there are workarounds but make things easy for yourself when you're learning and set the matching column as the leftmost column).
3. The number of columns between the match column and the return column. If they're next to each other, this number will be 2. If there's another column in-between (e.g. 'Name', 'Email', 'Phone'). This number will be 3.
4. The match type. This one is easy, always use the value FALSE. If you put TRUE you can have partial matches, which isn't a good idea.
So you almost know everything you need to know about Vlookups. There's just three more things I'd recommend knowing about.
1. Absolute cell references. This is useful for VLOOKUPs when defining your match/return grid (point 2 above). What it means is the grid doesn't change as you move the formula. You can tell the grid is locked when you see 4 dollar signs (e.g. $F$2:$G$20). You can cycle between relative and absolute cell references when editing the formula by using the F4 key, but for now just type in the dollar signs if they're missing.
2. When getting your data ready to match against, there are a few useful formulas for tidying up data. TRIM is often useful (it gets rid of leading and trailing spaces). UPPER (and LOWER and PROPER) are also useful if the data is a little messy and you need to make text data have a consistent case (e.g. matching against all uppercase letters). LEFT, MID, RIGHT and FIND are all useful if you want to create a lookup column from more complex data. The only other tip worth knowing when you start out creating a lookup column is to make a mental note of the format of the data, for example a number in number format won't match against a number in text format. If you want a quick way to change numbers stored as text back to regular numbers, enter a formula which takes the number formatted as text and multiplies it by 1.
3. Sometimes you'll want to check that your lookup formula is working correctly. Other than looking for #N/A values (which means the data hasn't been found) or 0 values (which means a match was found, but the return cell was blank), a good way to test is to just create another VLOOKUP that checks the logic in reverse, e.g. If a Vlookup formula is on Sheet1 and is referencing values in Sheet2, can you create a Vlookup formula in Sheet2 that confirms the right values made it to Sheet1. To be honest this last step might be too much when you're starting out, looking for #N/A values and 0 values should be fine.
Hope this helps. Vlookups were the first thing I learned in Excel that gave me the confidence to explore more, and have proven invaluable time and time again. Any questions, please ask.
Sometimes numbers stored as text are useful. For example, if you have telephone numbers in Excel, if the numbers aren't stored as text then leading zeros are dropped (e.g. 01234567890 would change to 1234567890).
Also, it's easy enough to see when numbers are stored as text (via visual cues and the format type field).
That said, perhaps the formulas could be made more flexible. Excel formulas are somewhat Lisp-like, it'd be great if Excel had Lisp-style macros for customising formulas. Probably won't happen though. Microsoft is developing the 'Power BI' side of Excel, I could see it becoming more common to create custom functions with M or DAX when the related editing functionality improves.
It help on thinking VLOOKUPS as something of the past once you install Excel16 and get to know the DataModel thingie. It really saves you a ton of excel coding everyday.
You mean the Power BI functionality (PowerQuery, PowerPivot, etc...)? Yeah, it's useful, but I'd suggest VLOOKUPs are more beginner-friendly and better for quick data manipulation.
That said, if you want to go from an Excel beginner to an intermediate user, then the Power BI functionality is definitely worth exploring.
It's also worth knowing how to work with Excel tables, which can help with making formulas more robust. However, all of this putting the cart before the horse, VLOOKUPs are too useful to skip over, and are a fundamental part of data manipulation with Excel formulas. A grounding in Excel formulas is useful before moving on to the fancier stuff.
I find INDEX and MATCH to be more useful / flexible than VLOOKUP. The only issue is a lot of people get freaked out when they see a INDEX / MATCH statement and, instead of trying to learn what it does, claim that your model is wizardry and don't feel comfortable using it without your supervision.
Combining INDEX and MATCH is more versatile than VLOOKUP as you can do things like have the result you return to the left of the value you're looking up. However, VLOOKUP / HLOOKUP are simpler to understand when starting out, and that's a good thing when you're trying to build confidence in using Excel.
Comments
Yes please. I use spreadsheets a ton but am lost when it comes to VLookup, pivot tables, linking to different sheets. Will be watching to see where this goes.
From a business standpoint I could see spending a few dollars to learn more about spreadsheets. Yes, these resources are available in other places (there may even be an organized set of Spreadsheet tutorials out there) but I didn't see that on HN this morning.
If it helps, try to think about VLOOKUP as the same method you used to use when looking up information in a phone book. To start, all you know is the name, and you want to lookup the phone number associated with that name.
There are four parts to a VLOOKUP formula...
1. The cell that contains the information you know (e.g. someone's name).
2. The grid of cells that contains the information you want to match against and the information you want to return (e.g. a table with names of people and their phone numbers). The first row of this grid should always be the list you want to match against, and the things you want to return should always be to the right of this (e.g. if the column order is 'Names, PhoneNumber' the Vlookup can work, if it's 'PhoneNumber,Names' then it won't, there are workarounds but make things easy for yourself when you're learning and set the matching column as the leftmost column).
3. The number of columns between the match column and the return column. If they're next to each other, this number will be 2. If there's another column in-between (e.g. 'Name', 'Email', 'Phone'). This number will be 3.
4. The match type. This one is easy, always use the value FALSE. If you put TRUE you can have partial matches, which isn't a good idea.
So you almost know everything you need to know about Vlookups. There's just three more things I'd recommend knowing about.
1. Absolute cell references. This is useful for VLOOKUPs when defining your match/return grid (point 2 above). What it means is the grid doesn't change as you move the formula. You can tell the grid is locked when you see 4 dollar signs (e.g. $F$2:$G$20). You can cycle between relative and absolute cell references when editing the formula by using the F4 key, but for now just type in the dollar signs if they're missing.
2. When getting your data ready to match against, there are a few useful formulas for tidying up data. TRIM is often useful (it gets rid of leading and trailing spaces). UPPER (and LOWER and PROPER) are also useful if the data is a little messy and you need to make text data have a consistent case (e.g. matching against all uppercase letters). LEFT, MID, RIGHT and FIND are all useful if you want to create a lookup column from more complex data. The only other tip worth knowing when you start out creating a lookup column is to make a mental note of the format of the data, for example a number in number format won't match against a number in text format. If you want a quick way to change numbers stored as text back to regular numbers, enter a formula which takes the number formatted as text and multiplies it by 1.
3. Sometimes you'll want to check that your lookup formula is working correctly. Other than looking for #N/A values (which means the data hasn't been found) or 0 values (which means a match was found, but the return cell was blank), a good way to test is to just create another VLOOKUP that checks the logic in reverse, e.g. If a Vlookup formula is on Sheet1 and is referencing values in Sheet2, can you create a Vlookup formula in Sheet2 that confirms the right values made it to Sheet1. To be honest this last step might be too much when you're starting out, looking for #N/A values and 0 values should be fine.
Hope this helps. Vlookups were the first thing I learned in Excel that gave me the confidence to explore more, and have proven invaluable time and time again. Any questions, please ask.
Do spreadsheets need loose[r] typing? It's it ever necessary to operate with a formula on numbers-as-text?
Sometimes numbers stored as text are useful. For example, if you have telephone numbers in Excel, if the numbers aren't stored as text then leading zeros are dropped (e.g. 01234567890 would change to 1234567890).
Also, it's easy enough to see when numbers are stored as text (via visual cues and the format type field).
That said, perhaps the formulas could be made more flexible. Excel formulas are somewhat Lisp-like, it'd be great if Excel had Lisp-style macros for customising formulas. Probably won't happen though. Microsoft is developing the 'Power BI' side of Excel, I could see it becoming more common to create custom functions with M or DAX when the related editing functionality improves.
It help on thinking VLOOKUPS as something of the past once you install Excel16 and get to know the DataModel thingie. It really saves you a ton of excel coding everyday.
You mean the Power BI functionality (PowerQuery, PowerPivot, etc...)? Yeah, it's useful, but I'd suggest VLOOKUPs are more beginner-friendly and better for quick data manipulation.
That said, if you want to go from an Excel beginner to an intermediate user, then the Power BI functionality is definitely worth exploring.
It's also worth knowing how to work with Excel tables, which can help with making formulas more robust. However, all of this putting the cart before the horse, VLOOKUPs are too useful to skip over, and are a fundamental part of data manipulation with Excel formulas. A grounding in Excel formulas is useful before moving on to the fancier stuff.
I find INDEX and MATCH to be more useful / flexible than VLOOKUP. The only issue is a lot of people get freaked out when they see a INDEX / MATCH statement and, instead of trying to learn what it does, claim that your model is wizardry and don't feel comfortable using it without your supervision.
Combining INDEX and MATCH is more versatile than VLOOKUP as you can do things like have the result you return to the left of the value you're looking up. However, VLOOKUP / HLOOKUP are simpler to understand when starting out, and that's a good thing when you're trying to build confidence in using Excel.