Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want a pretty complicated puzzle and I'm not entirely sure it is possible, I would like to try and avoid making a macro and just use the functions but...

I want to have a table of information, 1 column in Width X rows in Height, based on another table that is 10 columns by 20 rows I want the function to look down table 2 to row Y (designated by a single cell) and read across and, say it arrives at 5, go to table 1 and display the information in Cell A1, A2, A3, A4, A5 separated by a comma

now each function exists (Vlookup does most of it, there's a Text to Columns feature that if I could reverse would be perfect) but putting them all together is proving very complicated.

A series of Vlookup functions could do it but I don't know how to say "continue to repeat this X number of times" without VBA

share|improve this question
    
Trying to draw a picture to grok this... If Table 2, Row Y, returns 3, do we only display A1,A2,A3 in Table 1's corresponding Row X? How is 'Y' determined, what are we comparing to perform the Vlookup? Or are we displaying data (A1-A?) from Table 1's single column? –  lornix Jul 1 '12 at 16:09
add comment

1 Answer

There's no inbuilt function in Excel to concatenate a range so you'll either need to use VBA, download Morefunc add-in and use MCONCAT function, which can concatenate a range, or string together a bunch of IFs.

I'm not clear how you are getting the 5 but essentially if that value is x it could be something like this

=SUBSTITUTE(IF(ROW(A1)<= x,","&A1,"")&IF(ROW(A2)<= x,","&A2,"")&IF(ROW(A3)<= x,","&A3,"")&IF(ROW(A4)<= x,","&A4,"")&IF(ROW(A4)<= x,","&A4,"")&IF(ROW(A5)<= x,","&A5,"")&IF(ROW(A6)<= x,","&A6,""),",","",1)

extend as required, replace x with whatever function retrieves that value (or a cell reference containing that function)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.