Lookup non consecutive columns

[ozbeachbum]

Hi,
I have two ranges: L214:L232 & T214:T232

I need a formula to lookup the last value from the combined ranges.

Thanks in advance.

 

[Eric W]

Try:

=LET(a,L214:L232,b,T214:T232,s,SEQUENCE(ROWS(a)),m,MAX(s*(a<>"")),n,MAX(s*(b<>"")),IF(m+n=0,"",IF(m>n,INDEX(a,m),INDEX(b,n))))

 

[Peter_SSs]

The requirement is not clear to me. For example, with my sample data, what is the "last value from the combined ranges"?

Is it "g" because that is the most right value in the highest row number with any value?
Or is it "z" because that is the last value in the right-most column of the two?
Or is it something else?

24 03 12.xlsm
	L	M						S	T
214	a								x
215	b								y
216	c								z
217	d
218	e
219	f
220	g
221
222
223
224
225
226
227
228
229
230
231
232
Last

 

[ozbeachbum]

Hi Peter,
In your example it currently would be G as I have nothing in COLUMN T at present.
I was using the following formula which only referenced COLUMN L and worked fined for that.
=LOOKUP(2,1/(L214:L232<>""),L214:L232)
In your example with a formula that would reference both COLUMN L & COLUMN T it would be Z. (There would be entries in each row of COLUMN L)
Tried a number of different options to no success, just could not work out how to bring COLUMN T into the formula.

%		Difference		Change Date		%		Difference		# C
5.82%		5.82%								00
6.07%		0.25%								Last Rate
6.32%		0.25%								6.32%	/=LOOKUP(2,1/(L214:L232<>""),L214:L232)

Tried to insert Mini Sheet again, thought I was going to have some success, it copies to the clipboard, but wont past to the thread.
Above was the best I could come up with to show the sheet.

Thanks for all your efforts with me.

 

[MARK858]

Tried to insert Mini Sheet again, thought I was going to have some success, it copies to the clipboard, but wont past to the thread.

If you are using the latest version of XL2BB (version v2.1) try checking the box the red arrow is pointing to, then click Mini Sheet, when the new box appears copy the data with Ctrl + C, then paste in the thread with Ctrl + V

 

[Peter_SSs]

In your example with a formula that would reference both COLUMN L & COLUMN T it would be Z. (There would be entries in each row of COLUMN L)

If I have understood that correctly, and assuming col T is not populated by formulas, try

=LET(t,T214:T232,r,IF(COUNTA(t),t,L214:L232),LOOKUP(2,1/(r<>""),r))

If col T is formulas, try

=LET(t,T214:T232,r,IF(COUNTIF(t,"?*"),t,L214:L232),LOOKUP(2,1/(r<>""),r))

 

[ozbeachbum]

Thanks to both Eric & Peter, both their formulas work.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.