Using array as the row number in INDEX function

I have searched everywhere for an answer but couldn't find one, so I figure it's time to ask.

Is it possible to use an array for the 2nd argument (row_num) in an INDEX function? I have been experimenting with it and cannot get it to work.

The formula I'm working with is the following:

{=SMALL(INDEX(F$1:F$190, MATCH(C$1:C$190, C$1:C$190, 0)), ROW())}

The MATCH function gives me an array of values, which are really just row numbers in column F. I want to be able to use the INDEX function in front of it so that I can easily turn the array of values into values in the F column. But it never works. Everything after the INDEX( always collapsing down into a single "". Is it not possible to use the INDEX function this way? An alternative I found is to use the LOOKUP function, like so:

{=SMALL(LOOKUP(MATCH(C$1:C$190, C$1:C$190, 0), ROW(C$1:C$190), F$1:F$190), ROW())}

This looks up the values in the first array produced by the MATCH function (which, again, are row numbers I want to extract from column F), then it matches these values to the array of ROW numbers, then matches those to the corresponding F column values. It seems like an unnecessary step. Can't I just somehow use the raw row numbers produced by the MATCH function, instead of having to pass them on to an intermediary array?

Any help is greatly appreciated!

I would upload an attachment, but the workbook I am using contains a lot of confidential information (I am using it for my job).

If the example I gave was too hard to understand, I will try to ask a simpler question.

I have an array, for instance {1, 4, 5, 7, 7, 7, 9}. I want to use each of those values to pull data from the corresponding rows. So I want to pull data from cells F1, F4, F5, F7, F7, F7, F9. That is why I was hoping to use the array {1, 4, 5, 7, 7, 7, 9} in an INDEX function. I could do something like =INDEX(F1:F9, {1, 4, 5, 7, 7, 7, 9}). But this doesn't work. Can you not use multiple values in the INDEX functions row_num argument? If not, are there any other ideas of how to take an array of values and use them to point to row numbers in a column to get those values?

Ultimately, I want to compare two arrays. The first array would be made up of values from Column F (dictated by the row numbers in an array, the question that brought me here). The other array it is being compared to... is another array.

I know that you can pass an array through the SMALL function to get an nth value. But I don't want that. I want to take an array, example being {1, 4, 5, 7, 7, 7, 9}, and transform it into the values found in the corresponding row numbers in the F column.

So, if the F column looks like this:

F
1 red
2 blue
3 purple
4 orange
5 black
6 white
7 brown
8 green
9 pink

I want to turn this array: {1, 4, 5, 7, 7, 7, 9} into this array: {red, orange, black, brown, brown, brown, pink}. Why I want to repeat 7 is inconsequential.

I'm looking for someway to take the array and output a new array like I show above, using the first array's values as row references to output cell values to the second array. Like I said, I can do this easily by using the LOOKUP function, the first array being{1, 4, 5, 7, 7, 7, 9}, being the look up values, having it look up each value in a second array C$1:C$9 (which looks up 1, 4, 5, 7, 7, 7, 9 and just outputs the positions {1, 4, 5, 7, 7, 9}, and then looking up those positions in a third array F$1:F$9, which gives me the final array {red, orange, black, brown, brown, brown, pink}. But like I said, is there a way to go from my first array {1, 4, 5, 7, 7, 9} straight to an array of the corresponding row values in F?

Thank you very much for your example. But I am kind of baffled as to how it works. I understand the functions you used, however when I view the final function (starting in cell S2) and evaluate it step by step in the with the Evaluate Formula tool, the second array in the formula, $Q$2:$Q$34, which represents an array of index numbers to pull from the $A$2:$A$34 array, it collapses down to 1. This makes sense, as 1 would be the first number in the array. However, when I evaluate that same exact formula in cell S3, again it collapses down to a 1, but it somehow pulls the 2nd value from the $A$2:$A$34 array. Likewise, in cell S4, it collapses down to 1 but pulls the 3rd value. Is there something going on behind the scenes that I can't see? There doesn't seem to be anything in the formula to indicate which index number from the $Q$2:$Q$34 it should be using, although I suspect it has something to do with the row number the formula is on. I even tried emulating the exact formula in column T, but that produced the results I would have guessed - since the $Q$2:$Q$34 collapses down to 1 regardless of what row it's in, it always returns the 1st value from the $A$2:$A$34 array. Could you explain this anomaly?

I am attaching the workbook you sent me. I created a worksheet on Sheet2 which accurately represents the problem I'm still trying to work out. You'll see the formula in column D. This gives me the exact results I am looking for. It just seems very convoluted, and I need help making it more efficient if possible. The final outcome is that I want a list of the values in column, without any blank cells in between, and if there are duplicate colors (for example YELLOW), to compare the corresponding values in column B, and if they are the same, then to only return one of the duplicate values, but if they are different, return both.

Sorry for the long post. Thanks for your help!

Tony Valko thank you!!! That is exactly what I needed, works perfectly!

Actually I had one last question for Mr. Shorty. In the worksheet that you uploaded, in the formula =INDEX($A$2:$A$34,$Q$2:$Q$34), what dictates which value from $A$2:$A$34 will be returned? The formula is copied down the S column, and the values returned correspond with the 1st value, 2nd value, and so on. But I don't see anything in the formula that dictates that. I used to seeing something like a ROW() function telling INDEX which value to pull. Can you explain?

Very interesting. I had no idea this feature existed. I am going to have to research it a bit. Thanks for your help!