# index formula (or something akin) help

#### cmancu

##### Board Regular
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.

### Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### jim may

##### Well-known Member
I assume the other 6 Cells are Blank, or how do you identify your 6 cells of interest?

#### cmancu

##### Board Regular
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

##### MrExcel MVP
Here is one way:

Select the range A6:A11 and then type in this formula:
Code:
``````=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

##### Board Regular
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

##### Board Regular
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

##### MrExcel MVP
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:
Code:
``````=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:
Code:
``````=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

##### MrExcel MVP
Try...

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

Code:
``=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:

Code:
``=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

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

Replies
0
Views
485
Replies
16
Views
343
Replies
6
Views
422
Replies
25
Views
715
Replies
4
Views
147

1,191,719
Messages
5,988,295
Members
440,148
Latest member
sandy123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back