Using INDEX / MATCH instead of VLOOKUP

Many of you may be familiar with using VLOOKUP to search for data on your spreadsheets; it's easy to use and easy to remember the arguments for the formulas.  So why should you switch to using the INDEX/MATCH combo instead?  Using INDEX/MATCH means you no longer have to be looking up on the leftmost column of an array and returning a value from somewhere on the right - instead you have the freedom to have your table set up with the columns in any order.  These functions work exactly the same in Excel as it does in Google Sheets. So let's jump into how to use it: The formula is nested into two parts, for the first part of the INDEX function, just highlight the array you want to get your final result from, then the second part of INDEX (after the comma) is MATCH, just click the cell that contains your lookup value, comma then highlight the array where you can...
Read More

VLOOKUP in Google Sheets

VLOOKUP in Google Sheets VLOOKUP is a very commonly used formula within business and is easy to use in Google Sheets.  Once you realise how powerful it can be, you will begin using it for more and more time-saving exercises that would have otherwise taken ten times longer.  The formula is exactly the same in Google Sheets and all versions of Excel; these screenshots are from Google Sheets. In this example we have a column of reference codes in Column A, and the associated addresses in Column B.  We need to return the correct address when we type in the reference code into cell C1. VLOOKUP has 4 “arguments” (parts that need to be filled in to make the formula work, separated by commas), these are: lookup value, table array, column index, and range lookup. In this example, we want the result to appear in cell D1.  So begin by clicking in cell D1 and and typing: =VLOOKUP( Google Sheets will then give you a handy...
Read More

VLOOKUP in Excel

VLOOKUP is a very commonly used formula within business.  Once you realise how powerful it can be, you will begin using it for more and more time-saving exercises that would have otherwise taken ten times longer.  The formula is exactly the same in all versions of Excel and with Google Sheets; these screenshots are from Excel 2013 version. In this example we have a column of reference codes in Column A, and the associated addresses in Column B.  We need to return the correct address when we type in the reference code into cell C1. VLOOKUP has 4 “arguments” (parts that need to be filled in to make the formula work, separated by commas), these are: lookup value, table array, column index, and range lookup.   In this example, we want the result to appear in cell D1.  So begin by clicking in cell D1 and and typing: =VLOOKUP( Excel will then give you a handy “tooltip” to remind you what information it needs to make...
Read More