Ask Your Question

How to select the Nth item in a list where N is stored in array B?

asked 2016-09-16 11:46:03 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

There are 100 cells in column A, each with a single (text) character. Column B contains 100 integers (N) in the range 1-100, with the possibility of duplications and with some numbers missing. I want each of 100 cells in column C to contain the contents of A as determined by each N in column B. eg. Col A contains a,b,c,d,e,f,g,h,...; Col B contains 1,2,3,5,4,4,1,1,...; Col C contains a,b,c,e,d,d,a,a,....

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-09-16 12:45:37 +0100

Lupp gravatar image

updated 2016-09-16 12:47:08 +0100

If your 100 is actually a fix number, and the texts in column A also start with the first row as the contents in column B do, you may use =INDEX($A1:$A100;$B1) in cell C1 and fill the formula down till C100. Regard the difference between absolute and relative addresing! Only the 1 of the cell addres $B1 in the formula above is relative.

This is a rather raw method. If you want to be more flexible use the OFFSET function. Feel free to come back with additional questions in this case.

(Generally: You may want to read the Calc guide. With respect to your question the versions do not differ.)

edit flag offensive delete link more


Thanks. I found that "=INDEX($A$1:$A$100,$B1)" worked well.

CaspaB gravatar imageCaspaB ( 2016-09-16 15:29:39 +0100 )edit

Of course. The question is if it will still work well after some enhancements to / scaling up / adaptions of / ... your sheets. OFFSET can take parameters controlling the size of the data range, e.g.
(You may check the 'Solved' mark next to the answer.)

Lupp gravatar imageLupp ( 2016-09-16 17:36:36 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-09-16 11:46:03 +0100

Seen: 234 times

Last updated: Sep 16 '16