+ Reply to Thread
Results 1 to 6 of 6

Need Row and Column in Multi D Array

  1. #1
    Registered User
    Join Date
    04-04-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    4

    Need Row and Column in Multi D Array

    I need to find the Row and Column of the value.
    As in the below case Row 5 and column 1, if value is 8 then Row 3 Col 3

    1 11 6 26 16 21
    2 12 7 27 17 22
    3 13 8 28 18 23
    4 14 9 29 19 24
    5 15 10 30 20 25

    if value is 5
    Find Row of value
    Find Column of value

  2. #2
    Valued Forum Contributor
    Join Date
    03-14-2012
    Location
    Arizona USA
    MS-Off Ver
    Excel 2000/2007
    Posts
    408

    Re: Need Row and Column in Multi D Array

    I do not understand what you are trying to do.

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Need Row and Column in Multi D Array

    No better example than here.
    http://www.contextures.com/xlFunctio...ml#IndexMatch2

  4. #4
    Registered User
    Join Date
    04-04-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Need Row and Column in Multi D Array

    suppose if I put 14 in perticular cell, then 14 should match to the array. and give me row number and col number
    e.g. for 14 it is row 4 and col 2

  5. #5
    Registered User
    Join Date
    04-04-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Need Row and Column in Multi D Array

    Quote Originally Posted by davesexcel View Post
    But match is with only single dimensional array. I am not been able to do with multidimentional.

  6. #6
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Need Row and Column in Multi D Array

    Hi BeingYogi,

    Try this with CTRL+SHIFT+ENTER, rather than just ENTER.

    Assume data is in A1:F5

    Row #

    =IFERROR(SMALL(IF(A1:F5=8,ROW(A1:A5)),1),"No Match")

    Column #

    =IFERROR(SMALL(IF(A1:F5=8,COLUMN(A1:F1)),1),"No Match")

    Change 8 to a cell reference where is the lookup value.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1