Solved

Using Match and Index function -Defining the array

Posted on 2014-03-16
6
538 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:upobDaPlaya
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now