Using Offset to get to the next instance of a variable?

0
votes

I am attempting to find the next instance of a variable in order to generate a list base on another variable:

Mkt ID
10  908
15  915
15  416
25  312
25  215
32  482

Similar to the above. There are two drop downs, one for market and one for ID. I want the user to be able to select a market and in the ID drop down have the data validation filter to that list of IDs respective to the market in the first drop down.Let's say the market dropdown is $G$2. Market is Column A, and ID is column B.

Here's the formula I have so far:

OFFSET(ADDRESS(MATCH($G$2,A:A,0),1),0,1,COUNTIFS(A:A,$G$2),1)

This formula references the market, offsets by 0 rows and 1 column, counts the number of that market instance for height, and 1 row in width. I do not see why this is not working. Excel just gives the typical, are you really trying to type a formula? error code.

1
excelexcel-formula
No, that's not it. The address function may make it look that way, but I checked it thoroughly on that front - Devin
ADDRESS(MATCH($G$2,A:A,0),1) should be indirect(ADDRESS(MATCH($G$2,A:A,0),1)) - user4039065

1 Answers

1
votes

ADDRESS returns a string that looks like a cell reference. You need INDIRECT to turn that into a real cell reference that OFFSET can use.

=OFFSET(indirect(ADDRESS(MATCH($G$2, A:A, 0), 1)), 0, 1, COUNTIFS(A:A, $G$2), 1)