I'm trying to do something in Excel without using VBA macros. I suspect it is possible, but have some up empty so far. Basically for values in a table, I'd like to retrieve a list of all values in Column A conditionally where Column B equals a value I provide.
For example I'd like a function that essentially returns a range/description of cells for Column A if Column B is equal to zero.
Column A Column B
1 0
2 0
3 1
4 0
would return a range describing cells a2, a3, and a5 (1, 2, 4). I'd prefer to be able to do this using a formula, and not manually using pivot tables. I would be willing to create a view of the data using pivot tables if that table could then be referenced via a function to give me the appropriate results. I am also able to sort Column B in any way if it makes it easier to do this.
Ultimately I need to pull out a random value from Column A that meet the criteria of Column B in case that matters in the final solution.
Thanks.
