Ugly Formula - Prettification please

geofspa

New Member
Joined
Jan 7, 2012
Messages
30
Office Version
  1. 365
Platform
  1. Windows
:confused:

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))))))))))))))))))))))))))))))))))))))))))))))
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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)
 
Upvote 0
Solution
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)
 
Upvote 0
=Vlookup(indirect("'Input;Result'!A6)-1,Result,2,false) ---- trying to take 1 from A6 results in the error
 
Upvote 0
=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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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?

Disable AdBlock

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
Back
Top