When I am using Excel formulas such as vlookup or index match (often combined with offset and/or Indirect), there will commonly be more than one instance of a result that I am looking for. Sometimes, I want to pick up the first instance, but other times I would like to pick up the 2nd, 3rd, 4th, nth instance. For example, let's say I am using a look-up function on a table with cities down the left, and sports team from those cities on the right. In this example, I want to know all sports teams associated with Chicago, not just the first or last one. I know there is a way to do this using an Array formula, which I have used. However, Array formulas are brutal to work with, especially when applying them to a big data set. Is there another workaround that allows you to specify the instance (2nd, 3rd, nth) it occurs without using an array?
rfelds3 karma
When I am using Excel formulas such as vlookup or index match (often combined with offset and/or Indirect), there will commonly be more than one instance of a result that I am looking for. Sometimes, I want to pick up the first instance, but other times I would like to pick up the 2nd, 3rd, 4th, nth instance. For example, let's say I am using a look-up function on a table with cities down the left, and sports team from those cities on the right. In this example, I want to know all sports teams associated with Chicago, not just the first or last one. I know there is a way to do this using an Array formula, which I have used. However, Array formulas are brutal to work with, especially when applying them to a big data set. Is there another workaround that allows you to specify the instance (2nd, 3rd, nth) it occurs without using an array?
View HistoryShare Link