Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using Match and Index function -Defining the array

Posted on 2014-03-16
6
Medium Priority
?
578 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 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

963 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