• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index & Match Or Alternative

E

Excel-Access

Member
Can someone please advise me why in the Analysis Tab in Cell C5 and D5, I am not geeting the desired result.

Over the weekend both Narayank and Somendra created an Offset formula that I have listed. When I copy this it is still not working

So basically all 3 options has failed.

Cell C5 should be £125,685 and D5 should be £135,265

Thanking everyone in advance
 

Attachments

  • index & match.zip
    44 KB · Views: 9
Don't you simply need: =IFERROR(INDEX('Dec 2013 YTD'!$F:$F,MATCH(Analysis!$B5,'Dec 2013 YTD'!E:E,0)),"")
 
Hui

Thank you but this does not give the desired result as it needs to match it on Cell A5 and B5.

By copying your formula the result is 2,134,425 but it should be 125,685
 
How about being more specific about what your doing and what the answer should be
 
I checked the formula in C5 and it looks like INDEX is overshooting by 1. Try subtracting 1 from the sum of the two MATCH functions.

- juanito
 
Put this in C6 and drag to D6:

Code: 
=OFFSET(INDIRECT(IF(C4="MIAP",ADDRESS(228,6,,,"Dec 2013 YTD"),ADDRESS(228,9,,,"Dec 2013 YTD"))),MATCH($B$5,'Dec 2013 YTD'!$E$228:$E$321,0)-1,0)

But please whenever you upload a workbook set its calculation to automatic mode else mention it.
 
@Excel-Access

My formula had considered your sample file which you have uploaded. When you apply the formula in your original file you should understand first how the formula is working and than you should change the ranges in the formula to give you the right result.
Put this formula in C5 and copy to D5.

=INDEX(OFFSET(INDIRECT(ADDRESS(MATCH($A5,'Dec 2013 YTD'!$E:$E,0)+1,5,3,1,"Dec 2013 YTD")),0,0,9,5),MATCH($B5,OFFSET(INDIRECT(ADDRESS(MATCH($A5,'Dec 2013 YTD'!$E:$E,0)+1,5,3,1,"Dec 2013 YTD")),0,0,9,1),0),MATCH(C$4,'Dec 2013 YTD'!$E$21:$I$21,0))


Just advise if any issue.
Regards,
 
Somendra

Thank you for this and you are correct it is my lack of understanding of using your formula - I have to ask this question so I can learn in future. In the tab Analysis in Cell G5 Property & equipment I have modified your formula to pick up Cell #N/A. Can you tell me why please so I can try and understand the formula?

=INDEX(OFFSET(INDIRECT(ADDRESS(MATCH($A5,'Dec 2013 YTD'!$E:$E,0)+1,5,3,1,"Dec 2013 YTD")),0,0,9,5),MATCH($F5,OFFSET(INDIRECT(ADDRESS(MATCH($A5,'Dec 2013 YTD'!$E:$E,0)+1,5,3,1,"Dec 2013 YTD")),0,0,9,1),0),MATCH(G$4,'Dec 2013 YTD'!$E$21:$I$21,0))
 
@Excel-Access

Look the formula
=INDEX(OFFSET(INDIRECT(ADDRESS(MATCH($A5,'Dec 2013 YTD'!$E:$E,0)+1,5,3,1,"Dec 2013 YTD")),0,0,20,5),MATCH($F5,OFFSET(INDIRECT(ADDRESS(MATCH($A5,'Dec 2013 YTD'!$E:$E,0)+1,5,3,1,"Dec 2013 YTD")),0,0,20,1),0),MATCH(G$4,'Dec 2013 YTD'!$E$21:$I$21,0))

This index(Array, Row Num, Col Num) function has 3 arguments
1. Array: this is the data set from where you want the data.
2. Row Num & Col Num : these are row and col of data array.

So, index will deliver the data from the array at the intersection of row num & col num.

Now in my formula the PINK portion is array, BLUE is row NUM and BLACK is col num.

Now since, in your original post before this you were defining your data in a set of 9 Rows and 5 Cols the array formed by the formula was the array of 9x5 but in your recent file you are going beyond 9 rows so I had change 9 to 20 (Green portion) of the formula.

So, the variable array is formed in INDEX array argument and row argument. Col num argument is a simple MATCH function.

So if you copy this formula for some other Column B and Column F value just see if that value is withing 20 row or more than that, if it is more than that increase 20 to more than that value.

Hope this will clear your doubt.

Regards!
 
You must log in or register to reply here.
Back
Top