Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Locating the column position of data in 1 array from another array

    please refer to the attached sheet.

    I am trying to find the position of B from Array 1 for the corresponding value of A in Array 2.

    For example:
    B2 is in column 1 of Array 2 (cell E3)
    B3 is in column 5 of Array 2 (cell H4).
    The value in B may appear more than once in Array 2 but it is relative to the corresponding value in column A so this needs to match.

    I did use VLOOKUP to identify the values are in the Array but I couldn't get the column number. I also used MATCH which returned the column number but I have to know which row the value is in for it to work.

    I would appreciate any assistance for a resolution.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Assuming you meant column 4 rather than 5 for B3, then in C2 enter:
    =MATCH(B2,INDEX($E$2:$J$10,MATCH(A2,$D$2:$D$10,0), 0),0)
    and copy down.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Outstanding Rory, thanks very much. I will apply this to my current work book and hopefully I can finalise this task.

    Really appreciate you taking the time to assist me mate, take care.
    AB

Posting Permissions

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