Finding column where data exists

[angiemeh]

I have a column (row 3) from C:Z some columns have data some do not. I want to find the column where the first data appears and then also the last.

I want to copy this formula down to the rest of the rows.

For example, row 3 may have numbers in column E, F,G and V. I want a formula to find the value and pull back what is in column E. Then with another formula I want to do the same thing, but pull back the value in V. Then, in row 4 numbers may be in H, P,Q,W and I want the value in H and the value in W.

 

[bk]

Are you saying you want ONLY the data from the first column whereever that happens to be? Will be a little tougher, but doable (easier with a macro).

My first suggestion was going to be to do a concatenation of all the columns in the row and you would return it all. But my second reading made me think that's not what you wanted.

I'm not clear on what you're saying

 

[angiemeh]

Yes, I only want the value of the 1st instance of the dataset and then the value of the last instance of the dataset. OR, if I knew the row number of the 1st instance and the row number of the last instance, I could do a h-lookup to get the value itself.

so if in column 'C' there was nothing, 'D' there was nothing, 'E' there was 1.2 'F' there was 4.5 and 'G' nothing and 'H' 7.9 and 'I' nothing. I would want 1.2 and 7.9 Whatever is the 1st and last values of the dataset.

 

[AdamL]

For the first value, Ctrl+Shift+Enter:

=INDEX($C3:$Z3,1,MATCH(TRUE,$C3:$Z3<>"",0))

For the last value, enter:

=LOOKUP(2,1/($C3:$Z3<>""),$C3:$Z3)

 

[angiemeh]

Great!
That worked.
I understand the 1st formula
I don't understand the Lookup, why 2? How does it work?

=LOOKUP(2,1/($C3:$Z3<>""),$C3:$Z3)

 

[AdamL]

I'll leave those sort of explanations to Aladin Akyurek:

http://www.mrexcel.com/forum/showthread.php?p=492425

Cheers
A

 

[angiemeh]

Thanks!
This is a good explanation (I will have to read it a few times for it to sink in 100%)

I Appreciate the help

 

[Aladin Akyurek]

Thanks!
This is a good explanation (I will have to read it a few times for it to sink in 100%)

I Appreciate the help

BTW, if you are interested in the last numeric value in the range of interest...

=LOOKUP(9.99999999999999E+307,$C3:$Z3)

would be zillion times faster than

=LOOKUP(2,1/($C3:$Z3<>""),$C3:$Z3)

 

[AdamL]

A zillion?

But point taken.