Solved

Using Match and Index function -Defining the array

Posted on 2014-03-16
6
547 Views
Last Modified: 2014-03-16
I am struggling with the below function.   It works, but I do not understand it..See link for page (Example 3)..  INDEX/MATCH

If I break it apart then INDEX(Table,,1) = 0 thus how can the MATCH function work if the Array results in a 0

MATCH(B7,INDEX(Table,,1),0)
0
Comment
Question by:upobDaPlaya
  • 3
  • 3
6 Comments
 
LVL 19

Expert Comment

by:regmigrant
ID: 39932720
By leaving out a specific row the function returns them all; in the example Index(table,,1) will return all the rows in the table for column 1 - ie: the array a2:a4. Then the second index does the same for the headers - returning b1:d1. The two matches together then give an index position in the overall table
0
 

Author Comment

by:upobDaPlaya
ID: 39932823
Why cant u leave out the second and third index functions.   Cant the second and third match functions give you 2 coordinates.  What value are the 2 inner index functions providing that the match functions cant....
0
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
ID: 39932852
Match won't work with a two dimensional array - it needs to know which column or row to look in and won't work if there's more than one.

Index is working as an Array function in this example - if you enter =index(Table,,1) in a cell and press 'CTR:-ENTER' (instead of 'Enter') you will see the first element of the array (Sweater). Drag and copy down two rows and the  'sweater' repeats and all the cells have the same formula as you would expect.
However if you select three cells together, click in the edit box and type the same function (with all three selected) when you press Ctrl-Enter you will see all three elements - index has returned a 3 element array and populated the three cells. If you look at each cell its the same function returning three different values but these three cells are now treated as a single formula covering a 3 element array - click on any one of them and add & "s" and all three become plural (assuming you remember to Ctrl-Enter)

In the example; Match compares a single cell to a one dimensional array and returns a number representing a matching position in the array - so the first Index returns the ARRAY A2:a4 and match looks for 'Pants' and finds it in position 3, then the second Index returns the ARRAY b1:d1 and match finds  'med' in position 2. Then the outer Index finds the cell at row 3 column 2 in the table. Without the inner Index we could not force Match to look in the first column and then the first row.

We could of course replace the index functions with a Match that looked at a specific row or column in the table :
=INDEX(Table,MATCH(B7,Table[Column1],0),MATCH(A7,Table[#Headers],0))

But using Index gives us a reference to the column rather than a fixed object so apart from being and example  he wanted to give it shows a formula that is not fixed to column 1 and row 1 of the table. For example: you can't 'substitute' [column2] into the formula based on another cell in the spreadsheet - because table headers are objects not strings (so you couldn't put = [Table["&D6&"]]" and get a result by putting Column2 into D6);  but the parameters of the index function are completely flexible so the same formula could be used to look at another column and row by using a cell:
=Match(b7,index(table,,d6),0) for example - you can put 2 into D6 and the Match function will search  column 2 of the table.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:upobDaPlaya
ID: 39932990
Hi regmigrant,

I think I understand.  In my attached worksheet though can you confirm what flexibility I am losing - See Cell C8  ?
ee-match-index.xlsx
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39933072
In your example there is no advantage because you are only looking at two criteria - the day and the person, if you had a much larger table and needed to find everyone who had worked 7 hours on day 3 and day 4 your formula would grow unwieldy quite quickly whereas the indexed version would be the same because it can return an array of all the results in one calculation.

Example 3 is also  simple to explain the principle but example 4 and the follow up link make the use more clear
0
 

Author Closing Comment

by:upobDaPlaya
ID: 39933405
Thanks your response was great and it was just a matter of re reading your response and looking at example 4 and the link..I did not look at the link  you mentioned until after your response
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question