• 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.

Second version of Index Function

D

DJ

Member
Hi All,


I am using this site since I joined my previous company's MIS department after my promotion and have learnt many functions and techniques. Your website is amezing and I suggest everyone to refer to this website.


I learnt Index function from this website, however, I am aware with only one version of this function. I still unable to use the second version of it.


The two versions of Index funtion are:

1. =INDEX(array,row_num,column_num)

2. =INDEX(reference,row_num,column_num,area_num)


I usually use the first version with Match function. However, I am unaware how to use second version. I tried to find it on google but didn't get any satisfactory result.


Kindly help and if possible please provide examples too.
 
It is all explained in Help. If reference refers to a multi-area range, you can specify which area withing the range that you find the intersect for


For example


=INDEX((A1:C6, A8:C11), 2, 2, 2)


Looks at row 2, column 2 of the area A8:C11, that is B9.


Can't say I have ever needed to use it myself.
 
Hi xld,


Thank you so much for quick reply, however, I have already refered to excel help and didn't find it too helpful. I am unable to explain but I am unable to use it in any of my reports but I think this will be good to learn this function. Hence I need some more examples and situations where I can use it.


Thanks,

DJ
 
Good day DJ You may find this link of help


http://www.mrexcel.com/articles/excel-vlookup-index-match.php
 
Hi bobhc,


Sorry but given link is not helpful as it explains the first version of Index function.


I need help on the second version of Index function which has syntex like

'=INDEX(reference,row_num,column_num,area_num)


Thanks,

DJ
 
Hi DJ,


When we have more then one area and we need to set Indexing for them, In place of ARRAY, you can use MultiDimensionalArray.. and in the last parameter you can set the Required ArrayTableSet.


For Example..

=INDEX((Sheet1!$B$2:$M$31,Sheet1!O2:Z31),MATCH($C$3,Sheet1!$A$2:$A$31,0),MATCH(Sheet2!$C$2,Sheet1!$B$1:$M$1,0),C1)


In place of C1 set the required area for lookup.. or you can sewt more than two AREA for lookup..


https://dl.dropbox.com/u/78831150/Excel/Second%20version%20of%20Index%20Function%20-%20DJ.xlsx


Regards,

Deb
 
Hi Debraj,


Thanks a lot for your time and help. I will check the link later as in office, I am unable to open it.


Thanks,

DJ
 
You may also want to have a read of : http://www.excelhero.com/blog/2011/03/the-imposing-index.html
 
You must log in or register to reply here.
Back
Top