Ugly Formula - Prettification please

[geofspa]

I have a spreadsheet that will concatenate up to 50 cells in a column and return the result.

The result returned depends upon the first empty cell in the column to be concatenated, I have the concatenation covered but to return the result I have one horrid looking formula - as below.

I know I can find the first empty cell in Column by CSE Formula {=MIN(IF(A3:A53="",ROW(A3:A53)))}

How can I use the row returned to make a lookup in the table to return concatenated values - something a lot neater than this.

Input;Result = column to be concatenated (A3:A53)

Result = concatenation table (result returned from here)

=IF(A5="",VLOOKUP('Input;Result'!A4,Result,2,FALSE),IF(A6="",VLOOKUP('Input;Result'!A5,Result,2,FALSE),IF(A7="",VLOOKUP('Input;Result'!A6,Result,2,FALSE),IF(A8="",VLOOKUP('Input;Result'!A7,Result,2,FALSE),IF(A9="",VLOOKUP('Input;Result'!A8,Result,2,FALSE),IF(A10="",VLOOKUP('Input;Result'!A9,Result,2,FALSE),IF(A11="",VLOOKUP('Input;Result'!A10,Result,2,FALSE),IF(A12="",VLOOKUP('Input;Result'!A11,Result,2,FALSE),IF(A13="",VLOOKUP('Input;Result'!A12,Result,2,FALSE),IF(A14="",VLOOKUP('Input;Result'!A13,Result,2,FALSE),IF(A15="",VLOOKUP('Input;Result'!A14,Result,2,FALSE),IF(A16="",VLOOKUP('Input;Result'!A15,Result,2,FALSE),IF(A17="",VLOOKUP('Input;Result'!A16,Result,2,FALSE),IF(A18="",VLOOKUP('Input;Result'!A17,Result,2,FALSE),IF(A19="",VLOOKUP('Input;Result'!A18,Result,2,FALSE),IF(A20="",VLOOKUP('Input;Result'!A19,Result,2,FALSE),IF(A21="",VLOOKUP('Input;Result'!A20,Result,2,FALSE),IF(A22="",VLOOKUP('Input;Result'!A21,Result,2,FALSE),IF(A23="",VLOOKUP('Input;Result'!A22,Result,2,FALSE),IF(A24="",VLOOKUP('Input;Result'!A23,Result,2,FALSE),IF(A25="",VLOOKUP('Input;Result'!A24,Result,2,FALSE),IF(A26="",VLOOKUP('Input;Result'!A25,Result,2,FALSE),IF(A27="",VLOOKUP('Input;Result'!A26,Result,2,FALSE),IF(A28="",VLOOKUP('Input;Result'!A27,Result,2,FALSE),IF(A29="",VLOOKUP('Input;Result'!A28,Result,2,FALSE),IF(A30="",VLOOKUP('Input;Result'!A29,Result,2,FALSE),IF(A31="",VLOOKUP('Input;Result'!A30,Result,2,FALSE),IF(A32="",VLOOKUP('Input;Result'!A31,Result,2,FALSE),IF(A33="",VLOOKUP('Input;Result'!A32,Result,2,FALSE),IF(A34="",VLOOKUP('Input;Result'!A33,Result,2,FALSE),IF(A35="",VLOOKUP('Input;Result'!A34,Result,2,FALSE),IF(A36="",VLOOKUP('Input;Result'!A35,Result,2,FALSE),IF(A37="",VLOOKUP('Input;Result'!A36,Result,2,FALSE),IF(A38="",VLOOKUP('Input;Result'!A37,Result,2,FALSE),IF(A39="",VLOOKUP('Input;Result'!A38,Result,2,FALSE),IF(A40="",VLOOKUP('Input;Result'!A39,Result,2,FALSE),IF(A41="",VLOOKUP('Input;Result'!A40,Result,2,FALSE),IF(A42="",VLOOKUP('Input;Result'!A41,Result,2,FALSE),IF(A43="",VLOOKUP('Input;Result'!A42,Result,2,FALSE),IF(A44="",VLOOKUP('Input;Result'!A43,Result,2,FALSE),IF(A45="",VLOOKUP('Input;Result'!A44,Result,2,FALSE),IF(A46="",VLOOKUP('Input;Result'!A45,Result,2,FALSE),IF(A47="",VLOOKUP('Input;Result'!A46,Result,2,FALSE),IF(A48="",VLOOKUP('Input;Result'!A47,Result,2,FALSE),IF(A49="",VLOOKUP('Input;Result'!A48,Result,2,FALSE),IF(A50="",VLOOKUP('Input;Result'!A49,Result,2,FALSE),Formula!C51))))))))))))))))))))))))))))))))))))))))))))))

 

[gaz_chops]

Maybe

=VLOOKUP(indirect("'Input;Result'!A"&MIN(IF(A3:A53="",ROW(A3:A53))))-1,Result,2,FALSE)

 

[geofspa]

Maybe

=VLOOKUP(indirect("'Input;Result'!A"&MIN(IF(A3:A53="",ROW(A3:A53))))-1,Result,2,FALSE)

Nice try --- returns #VALUE !

Still

 

[gaz_chops]

Did you enter as an array? Ctrl, Shift & Enter

 

[geofspa]

then #N/A error

 

[gaz_chops]

Hmm haven't got excel to test, think the -1 is in the wrong place, try

=VLOOKUP(indirect("'Input;Result'!A"&MIN(IF(A3:A53="",ROW(A3:A53)))-1),Result,2,FALSE)

 

[Eric W]

What kind of data do you have in A3:A53? Text or numbers? If it's text, and you're looking for the last value in A3:A53 to use in the VLOOKUP, then maybe:

=VLOOKUP(LOOKUP("zzzzzzzz",A4:A53),Result,2,0)

 

[geofspa]

=Vlookup(indirect("'Input;Result'!A6)-1,Result,2,false) ---- trying to take 1 from A6 results in the error

 

[geofspa]

=VLOOKUP(INDIRECT("'Input;Result'!A"&MIN(IF(A3:A53="",ROW(A3:A53)))-1),Result,2,FALSE)
=VLOOKUP(INDIRECT("'Input;Result'!A"&MIN(IF(A3:A53="",ROW(A3:A53)))-1),Result,2,FALSE)

So excited I pasted twice --- thanks this works

 

[gaz_chops]

the 6 is returned by MIN(IF(A3:A53="",ROW(A3:A53))), that is what we need to -1 from, so if it finds A8 is 1st blank, then lookup A7.