Return Index / Address of Multiple Values' Locations in a 2D Array

Josh-8092 35 Reputation points
2026-09-16T18:21:23.5+00:00

Hi all,

I have a table of Values with multiple rows and columns, each column with a header that indicates a Group. Each column shows which Value has been assigned to which Group, and can have any number of Values within it. For example:

group-array

On a second sheet, I have a master list of Values that's dynamically pulled from another source, and can be updated with new values from time to time. On that sheet, I need to have each Value display which Group it's been assigned on the original sheet, like this:

value-list

My goal is to have a formula in the Group column on this sheet that reads the Value name, finds the index / address of the value in the other sheet, and pulls the Group name from the header. I found this helpful question and answer thread that dealt with this, and the responder gave this formula as a solution:

=INDEX($1:$1,SUMPRODUCT(([array]=[lookup value])*COLUMN([array])))

I tried this out, and it worked perfectly for one Value. I was able to pull the Group name with an INDIRECT function, and it's fine. However, this only works for one value, not a whole list like what I have. I'd like to find an array formula I can put in the top cell of the Group column and have it dynamically display the Group name for each Value in that list. The idea behind this is that when a new Value is added, seeing no Group assigned will prompt us to go to the assignment sheet and put it in a Group.

I tried adapting that formula with a BYROW function, or simply putting the whole column of values in the [lookup value] spot, but neither worked. Any help would be greatly appreciated. Thank you!

Microsoft 365 and Office | Excel | For business | Windows
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Barry Schwarz 6,191 Reputation points
    2026-09-17T04:35:58.0233333+00:00

    If you data is in E11:I16 and your list of values starts in K4, then enter the following formula in L4

    =LET(d,$E$11:$I$17,
     o,COLUMN(INDIRECT(CELL("address",d))),
     f,TOCOL(IF(d=K4,ADDRESS(ROW(d),COLUMN(d),4),NA()),2),
     INDEX(d,1,COLUMN(INDIRECT(f))-o+1))

    and copy it down to the last value in columns K.

    • d is the range of data to search.
    • o is the offset to the first column of d.
    • f is the address of the cell where the match was found.
    • INDEX picks up the value at the top of the column where f is located.

    Was this answer helpful?

    0 comments
  2. Ashish Mathur 102.4K Reputation points Volunteer Moderator
    2026-09-16T23:09:40.7+00:00

    Was this answer helpful?

    0 comments
  3. Jay1 Tran 1,380 Reputation points Independent Advisor
    2026-09-16T21:58:28.2533333+00:00

    Hi Josh-8092,

    Thank you for reaching out.

    To return the Group assigned to each Value, enter the following formula in cell B2 and copy it down the Group column:

    =IF(A14="","",
 IFERROR(
  INDEX(Sheet1!$A$1:$E$1,1,
   XMATCH(TRUE,
    BYCOL(Sheet1!$A$2:$E$1000,LAMBDA(c,OR(c=A14)))
   )
  ),
  "Not assigned"
 )
)

    Before entering the formula, replace Sheet1 with the actual name of the worksheet containing the Group headers and assigned Values.

    The formula reads the Value in A2 and checks each Group column on the specified worksheet. When it finds the Value, it returns the header from the matching column, such as Group 1, Group 2, or Group 3. If the Value has not been assigned to any Group, the formula displays Not assigned, making it easier to identify Values that still require an assignment.

    After confirming that the formula returns the expected result in B2, copy or fill it down for the remaining Values. The reference to A2 will update automatically for each row.

    User's image I hope the information I shared earlier was somewhat helpful in addressing your issue. If you have any further questions or updates, please don’t hesitate to share. I’m always happy to assist further if needed.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.