Solved

Using Match and Index function -Defining the array

Posted on 2014-03-16
6
564 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
Independent Software Vendors: 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

623 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