Thread: Index Function with changing array

Hello,

I'm going to have trouble explaining this. I'm not very experienced with the index function but I think it could solve my problem if used correctly. I'm having trouble determining the array.

To take a step back, I use a vlookup with a bunch of concatenated values. It is an uncommon occurence, but there are times when there are duplicate values, so the vlookup will always return the first value. Here is the thing - the lookup value follows the same order as the vlookup table array.

So, lets say there are 5 lookup values which are duplicates. The table array will have the 5 values I'm trying to return in a row. That is why I think an index formula could work. My problem is that the data in the table array contains thousands of lines and the potential duplicates (possibly ranging anywhere from 2 to 100 of the same value) could begin on any row in the table array. I've posted a simplified version of what I am trying to do. And as usual, I truly appreciate the help. For people who respond and solve problems, I owe you more than you probably realize. haha. I probably indirectly owe you royalties.

For the example, Sheet1 F3 is where the formula would be. It would search for a value on Sheet 2 returning a value from column B in sheet 2. You should see that cells F6 through F8 would return "grape" with a simple vlookup. I'd like to use an index function to return grape, tomato, and than squash in F3:F5 - but only when the lookup value is duplicated.

I forgot to add - in reality sheet 2 would have thousands of lines of data, mostly useless, but the duplicated lookup values could be anywhere.

You can use a COUNTIF of matching values so far, which will increment 1 at a time, i.e. in F3 copied down

=INDEX(Sheet2!B$1:B$9,MATCH(E3,Sheet2!A$1:A$9,0)+COUNTIF(E$3:E3,E3)-1)