Using array as the row number in INDEX function

[SinJinQLB]

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!

[Nisha Dhawan]

Hi,

Can you please upload the attachment for my better understanding.

Thanks
Nisha

[SinJinQLB]

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?

[Tony Valko]

What condition is met to arrive at the array {1, 4, 5, 7, 7, 7, 9} ?

Why would you want to repeat 7?

The typical method for doing this is to pass the nth element of the array to the SMALL function since you can only return a single result to a single cell (unless you're using some form of VBA).

Index(range,SMALL(IF(condition,ROW(range)),N))

Where ROW(range) is the array.

You really haven't explained what you're wanting to do.

[SinJinQLB]

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?

[MrShorty]

Since you cannot come up with a sample sheet, here's a randomized sheet that I put together, essentially using the same formula that you presented. It seems to work just fine.

I would guess that there is something else going on that is not specific to whether or not you can use an array as the row number argument in the INDEX() function.

We don't want you to upload confidential information. One very useful skill for getting your question answered quickly and efficiently, though, would be knowing how to create suitable "dummy" data that will provide a context for your questions and problems. As you can see, I like to just use random numbers for dummy data. Putting some meaningless data into a small sample spreadsheet, with a good description of what you want to accomplish along with what you have tried that doesn't work (and why it doesn't work), will usually go a long ways towards helping us understand your requirements.

[SinJinQLB]

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!

[MrShorty]

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 would guess that it is some bug or "feature" of the formula evaluate tool that prevents it from showing the arrays during evaluation for this situation. Beyond that, I'm not sure I know why.

So, the final goal of this INDEX() function is to create a list of unique entries from the desired column. What comes next after the list of unique entries? In some ways, this starts to look like "database" work, and I begin to wonder if something like a pivot table would be a more efficient approach. I, personally, do not know how to use pivot tables, but I can quickly create a pivot table and have it generate the list of unique entries. If you are not forced by some other constraint to use formulas, you might consider a pivot table.

[Tony Valko]

Sounds like you want this...

http://excelxor.com/2014/09/05/index...alues/#more-98

[SinJinQLB]

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

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[SinJinQLB]

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?

[MrShorty]

Have you tried to edit the formula in column S? If you try to edit any individual element in column S, you should get an error/message box that says "you cannot change part of an array", because I entered the formula in column S as one array formula. The same idea used by the LINEST(), TRANSPOSE(), MINVERSE(), FREQUENCY() and similar array functions where the output of the function is, itself, an array. The thing that dictates which value is returned to a given cell is determined by its position in the array.

If you have not worked with functions that output arrays like those mentioned, then this will likely take some extra effort to follow what the function is doing.

[SinJinQLB]

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