NKW6 Pt 3 INDEX and MATCH

 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 has a match within that range.  It then returns a number.  Why is this so useful?  Well unlike Vlookup, the lookup value does not need to be in the first column.  Secondly you are using a specific match to specify the column number and therefore if the order of the table columns change, it will not impact the function (as long as the matching column has not been deleted that is).  Let us look at each of these in turn and then combine them.

 

·         =INDEX(A1:N5600,1,1)

·         The =INDEX tells the programme what function you are using

·         All functions are enclosed in brackets ()

·         The A1:N5000 is the table array or range where the programme will look. 

·         The 1 is specified row number in the array

·         The 1 is the specified column number in the array

·         =MATCH(B6,F1:F5600,0)

·         The =MATCH tells the programme what function you are using

·         All functions are enclosed in brackets ()

·         The B6 is the lookup value. 

·         The F1:F5600 is range that the programme will search (in this case it will identify a row number as it is searching rows 1 to 5600 in column F)

·         The 0 is the specified that the match needs to be exact and not approximate (1), this is the same as the VLOOKUP

The next step is to combine both functions:

·         =INDEX(A1:N5600, MATCH(B6,F1:F5600,0), MATCH(“Qty”,A1:N1,0))

·         So we have now adapted the formula to replace specified column and row numbers with flexible MATCH functions looking for order number (entered into B6) with Qty.  It is critical that both the order number and exact spelling of Qty is matched in the table otherwise an error will be shown.  You should use the IFERROR with these as well.

As before, a good example is worth doing in Excel itself so you can see how the functions are working and get a feel for their use.  The video below will take you through the steps.




Comments

Post a Comment

Popular posts from this blog

NKW6 PT1 - IF

Image
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 wh...
Read more

Power Query: Merge and Append

Image
  Power Query: Merge and Append Discover the efficiencies and benefits of connecting and combining your data sources to drive your governance and performance measurement! Don`t be put off by the terminology, Power Query is simply sorting and organising data that you wish to import into your Excel spreadsheet and in doing so you can save a lot of time and reduce the scope for errors. ; The merge and append bit is talking about taking different data sources and adding them together to form a single source from which you can then do all sorts of interesting things! First of all we need to define what we mean by  merge  and  append . ; To  merge  a data set in this case means to add one data set to another by adding additional fields. For example: If we have a table of data that contains details of employee departments we might want to add it to another data table that contains details of their salaries. ; In effect we will create a single table ...
Read more