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 )
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Example 3 is also simple to explain the principle but example 4 and the follow up link make the use more clear
ASKER
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