NKW1709 - Excel

 Part 3 INDEX and MATCH The last in a set of three looking this time at INDEX and MATCH as a way to search a table and return the contents of a cell.  This combination is a more versatile and perhaps more robust method of looking up data in a table than VLOOKUP. When I look at these two functions I almost always use them together.   It looks somewhat complex but thinking through and breaking it down it becomes clear and there are significant advantages.   First of all indexing is a method of pointing to something so if you imagine a table and you tell the programme which row to go to and which column to align to, you end up and an intersection and the contents of the cell are returned.   This may have it’s uses but it is rather inflexible on its own. By introducing MATCH to replace a specified row or column number you are now making the function so much more flexible and powerful.  The MATCH function looks at a range (either a column or row) and then sees whether the specified value

Part 2 VLOOKUP In this section I examine the fundamentals of VLOOKUP.  So what is VLOOKUP?  Well it is a way of searching a table for information based on a reference value and then returning the content from a specified column.  We can use this to search a reference table for example a list of clients in a database or we can use it to auto populate a form based on a reference.  The key aspect here is that there needs to be a unique reference identifier and the column containing the identifier needs to be the first column.   Let’s see what this looks like when written out: ·          =VLOOKUP ( P1 , A1:N5000 , 5 , 0 ) ·          The =VLOOKUP tells the programme what function you are using ·          All functions are enclosed in brackets () ·          P1 is the lookup value (this is what the programme will seek out from the table.   In this case it is a cell reference but could easily be some text or a figure ·          The A1:N5000 i s the table array or range where t

Introduction I recently pulled together an Excel session to go through the fundamentals of the IF, VLOOKUP, INDEX and MATCH functions.  To do this I started out by putting together a bit of an explanation and then recording, with sample data, how these functions can be used and common pitfalls.  This is the first session. Part 1 IF The IF function can allow you to interrogate the information in a cell or cells and then instruct the programme to return a response, which can be text a number, a cell reference, another function or a calculation.  It sounds rather complex, another way to think about it might be to set yourself a problem for example If the total cost of a project exceeds £3m then I should abandon it otherwise I will go ahead.  How does this look when written out?   ·          =IF ( A1 > 3000000 , ”Abandon” , ”Proceed”) ·          The =IF tells the programme what function you are using ·          All functions are enclosed in brackets () ·          A1 is whe