+ Reply to Thread
Results 1 to 4 of 4

Arrays, Index, Match on multiple rows

  1. #1
    Registered User
    Join Date
    11-17-2020
    Location
    Springfield, MO
    MS-Off Ver
    Office 201
    Posts
    2

    Arrays, Index, Match on multiple rows

    Hello,

    I am having difficulty in getting arrays to work and am paring down my problem to the basics.

    What I am trying to do is find information in one cell and return the information in the cell below it.
    I have the basic formula that works for one row, this is in the cell B4 and copied down the row:
    offset(index($b$13:$m$13,0,match(b3,$b$13:$m$13,0)),1,0)

    It works as intended, including the errors. My problem is that while I'll know the row the information could reside, I won't know which row the information is in.
    I tried an array that looked like such:
    {=offset(index($b$13:$m$13&$b$18:$m$18,0,match(b3,$b$13:$m$13&$b$18:$m$18,0)),1,0)}
    comes back with #N/A, and when stepping through the information gathered looks off.

    what am I doing wrong?

    The enclosed workbook is approximate to the setup we have. Top is set as a backplane in a terminal room, bottom is set how our switches have their ports.
    I am trying to find the outlet that matches on the backplane & switch. If a match is found, put the device information under the match to the cell beneath the outlet on the backplane.

    Thanks for any suggestions or help you can provide.
    Ken
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    08-12-2020
    Location
    Atlanta, Georgia
    MS-Off Ver
    Office 16
    Posts
    127

    Re: Arrays, Index, Match on multiple rows

    hi Nelsonk,
    If I understand this correctly, you are wanting row 3 and row 8 to look at rows 13 and 18, and if there is a match, put the value below it in the device lines rows 4 and 9 in the backplane.
    I took your basic formula and extended it to offset and match both lines. In B4, =IFERROR(OFFSET(INDEX($B$13:$M$13,0,MATCH(B3,$B$13:$M$13,0)),1,0),OFFSET(INDEX($B$18:$M$18,0,MATCH(B3,$B$18:$M$18,0)),1,0))
    The drag it across, and then copy paste into B9, the drag it across. It simply looks in row 13 and if it finds N/A, looks in row 18.
    Squeaky

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    28,655

    Re: Arrays, Index, Match on multiple rows

    Rather than use OFFSET, which, being volatile, recalcualtes every time ANYTHING changes in the sheet, use INDEX-SUMPRODUCT...

    In B4, copied across:

    =IFERROR(INDEX($A:$M,SUMPRODUCT(($B$13:$M$18=B3)*ROW($B$13:$M$18))+1,SUMPRODUCT(($B$13:$M$18=B3)*(COLUMN($B$13:$M$18))))&"","")

    Then select the block and copy/paste to row 9.
    Attached Files Attached Files
    Glenn



  4. #4
    Registered User
    Join Date
    11-17-2020
    Location
    Springfield, MO
    MS-Off Ver
    Office 201
    Posts
    2

    Re: Arrays, Index, Match on multiple rows

    Thank you both for your responses. They work for me.
    I'm going to chew through them and work on if they can be expanded as the sheets have more than 1 sheet & multiple items I need to search.

    I already know I'm in for a headache, but that's the nature of my job.

    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Querying Large Series of Arrays And Displaying Unique Values in Arrays [Hard Index Match?]
    By larryg003 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-28-2020, 04:52 PM
  2. Index match multiple arrays
    By lukeareed in forum Excel General
    Replies: 1
    Last Post: 04-27-2019, 07:25 AM
  3. [SOLVED] Index match function with multiple arrays
    By georgedixon in forum Excel General
    Replies: 6
    Last Post: 09-15-2017, 01:49 AM
  4. INDEX/MATCH with multiple arrays. Possibly need IF Function as well?
    By guy lafleur in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-31-2015, 03:13 PM
  5. INDEX & MATCH Function with Multiple Arrays
    By btone in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-22-2014, 03:59 PM
  6. Problem with reference to multiple arrays in INDEX/MATCH combo
    By tnuis in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-24-2013, 08:10 PM
  7. Problems with reference to multiple arrays using VLOOKUP / INDEX-MATCH
    By tnuis in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-24-2013, 07:21 AM

Tags for this Thread

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