Link to home
Start Free TrialLog in
Avatar of upobDaPlaya
upobDaPlaya

asked on

Using Match and Index function -Defining the array

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)
Avatar of regmigrant
regmigrant
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of upobDaPlaya
upobDaPlaya

ASKER

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....
ASKER CERTIFIED SOLUTION
Avatar of regmigrant
regmigrant
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
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