Return Index / Address of Value in 2D Array Down Multiple Rows

Question

Return Index / Address of Value in 2D Array Down Multiple Rows

Josh-8092 35

Hi all,

I have a table with several rows and columns, each column with a header. This header is the name of a Group, and in the table are several different Values, each assigned to a Group. Each Group column has varying numbers of Values. For example:

group-array

On a second sheet, there is a master list of Values, and I'm trying to automatically pull the Group name each Value is assigned, like this:

value-list

This list of Values on the second sheet is dynamically populated from another source, and can be updated from time to time, with new ones being added and old ones being removed. The idea is that I'd like to have this master list display which Group each Value is assigned, and if a new Value shows up on this list that isn't assigned to a Group, it'll prompt us to assign it to one.

Looking around, I found this helpful question and answer thread about doing this for a single value. In that answer, the responder provided this formula to use:

=INDEX($1:$1,SUMPRODUCT((A2:P200="Particular Value")*COLUMN(A2:P200)))

Where A2:P200 is the array, and Particular Value is what I'm trying to find. I tried this, and it worked perfectly to pull the column address. I was able to grab the Group name via an INDIRECT function, and it's fine.

However, I don't want to have this formula hardcoded down a set number of rows. I'd much rather have an array formula that I can put at the top of the list, and it calculates down the whole list of Values, dynamically resizing based on how many rows there are to calculate.

I've attempted adapting that formula to include a BYROW function, but I can't get it to work. Any help with this would be much appreciated. Thanks!

0 comments

1 answer

Your answer

Answer 1

Dear @Josh-8092

I understand that your current formula successfully returns the column position/address for a matching value, and you can then use INDIRECT to retrieve the corresponding Group name. Since the issue appears to be related to extending this approach to your dynamically populated master list, I’d like to narrow down the exact requirement before suggesting a formula.

Could you please provide the following details?

Please share the actual formula you are currently using in the Group column, including the INDIRECT portion.

Please provide a small sample of the actual data layout, showing the Group headers and values, preferably as cell references (for example, A1:E200).

For a value that does not exist in the source table, what result would you like to display—blank, "Not Assigned", or something else?

Can the same Value appear under more than one Group? If so, should the formula return all matching Groups or only the first one?

Since the master list is dynamically populated, are you using an Excel Table, dynamic array formula, Power Query, or another source to generate that list?

Which version of Excel are you using (for example, Microsoft 365 desktop, Excel for the web, or another version)?

If possible, please provide 5–10 representative rows from the actual workbook (with any confidential information removed). This will help me reproduce the scenario and provide a formula that handles the dynamic additions/removals correctly.