index formula (or something akin) help

[cmancu]

I have 12 cells, A1:C4, up to any 6 of which may randomly contain values depending upon data entry. Using formulas only, I would like to find any of the 6 values entered in A1:C4 and display them in A6:A11, searching in order of A1, B1, C1... (across rows beginning with row 1)

I've been trying to enter formulas (index, choose, lookups,...) in A6:A11 to do the search and return, but it seems to get to complicated for me. I know it must be easier than I am making it.

Any help? Thanks much.

 

[jim may]

I assume the other 6 Cells are Blank, or how do you identify your 6 cells of interest?

 

[cmancu]

I'm sorry, yes, the other 6 cells of the range of 12 will be blank. Only up to 6 will be filled.

 

[Colin Legg]

Here is one way:

Select the range A6:A11 and then type in this formula:

=INDEX(A1:C4,SMALL(IF(A1:C4 <> "",ROW(A1:C4)),{1;2;3;4;5;6}),
    CHOOSE(SMALL(IF(A1:C4 <> "",{1,2,3;4,5,6;7,8,9;10,11,12}),
    {1;2;3;4;5;6}),1,2,3,1,2,3,1,2,3,1,2,3))

Complete the entry with CTRL+SHIFT+ENTER.

If there are less than 6 entries then outstanding items will be shown as #NUM!.

Hope that helps...

 

[cmancu]

Thank you, but I try that and get #REF! in each A6:A11. My values entered in A1:C4 will be either 2 letters, like LL, LS, or AN, or a combination such as XT15 or XC5. Does that matter?

 

[cmancu]

I'm sorry, my example using A1:C4 and A6:A11 was merely for simplification. I didn't at first realize the numbers in your formula refer to those values.

My actual data range on MY sheet is I80:K84 and the recipient range is A85, A86, A87 and then over to E85, E86, and E87.

I realize now that must make a difference. My apologies.

How would your formula differ based on this knowledge?

Thank you.

 

[Colin Legg]

Hi,

So you want the first 3 values in A85 to A87, and the next 3 values in E85 to E87?

Select range A85 to A87 and then put in this formula:

=INDEX(I80:K84,SMALL(IF(I80:K84 <> "",ROW(I80:K84)-ROW(I80)+1),{1;2;3}),
    CHOOSE(SMALL(IF(I80:K84 <> "",{1,2,3;4,5,6;7,8,9;10,11,12}),
    {1;2;3}),1,2,3,1,2,3,1,2,3,1,2,3))

Select range E85 to E87 and then put in this formula:

=INDEX(I80:K84,SMALL(IF(I80:K84 <> "",ROW(I80:K84)-ROW(I80)+1),{4;5;6}),
    CHOOSE(SMALL(IF(I80:K84 <> "",{1,2,3;4,5,6;7,8,9;10,11,12}),
    {4;5;6}),1,2,3,1,2,3,1,2,3,1,2,3))

Both CTRL+SHIFT+ENTER.

Hope that helps,

 

[Domenic]

Try...

A85, confirmed with CONTROL+SHIFT+ENTER, and copied down to A87:

=INDEX($I$80:$K$84,SMALL(IF($I$80:$K$84<>"",ROW($I$80:$K$84)-ROW($I$80)+1),ROWS(A$85:A85)),RIGHT(SMALL(IF($I$80:$K$84<>"",((ROW($I$80:$K$84)-ROW($I$80)+1)*1000)+(COLUMN($I$80:$K$84)-COLUMN($I$80)+1)),ROWS(A$85:A85)),3)+0)

E85, confirmed with CONTROL+SHIFT+ENTER, and copied down to E87:

=INDEX($I$80:$K$84,SMALL(IF($I$80:$K$84<>"",ROW($I$80:$K$84)-ROW($I$80)+1),ROWS(E$85:E85)+3),RIGHT(SMALL(IF($I$80:$K$84<>"",((ROW($I$80:$K$84)-ROW($I$80)+1)*1000)+(COLUMN($I$80:$K$84)-COLUMN($I$80)+1)),ROWS(E$85:E85)+3),3)+0)

Hope this helps!

 

[cmancu]

Great! Thank you much, folks. It obviously was a little tougher than I was capable of.
Regards.