Solved

Using Match and Index function -Defining the array

Posted on 2014-03-16
6
549 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
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;…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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