Hello,

I have looked around and I can't seem to find a formula that does what I want to do without using arrays.

What I want is a formula that can lookup the last value in either of two columns, and then return the corresponding value that is 10 columns over. What I am doing now is basically an IF function:

=IF(MAX(IF($B$2:$B33=B34,ROW($B$2:$B33)))>MAX(IF($C$2:$C33=B34,ROW($C$2:$C33))),LOOKUP(2,1/($B$2:$B33=B34),$K$2:$K33),LOOKUP(2,1/($C$2:$C33=B34),$L$2:$L33))

This finds the column where the last item in B34 is, and then LOOKUPs from bottom to top based on that. The trouble is that it uses arrays, and when translating between Excel and Google Sheets, (because my school uses Chromebooks) the formula gets corrupted. Also, arrays can be a pain, because of having to use ctrl-shift-enter every time I modify a function.

I figured a simpler way to go around this would be to have Excel find the last value in either of two columns using a two-column lookup range (and a two-column result range). Then, Excel would look 10 columns over to find the value. Does anyone have any idea to do this, and if not using this method, another way?

Also, please do not simply hand me a formula without explaining it. I would like to know why it is the way it is. For example, I have no idea why the =LOOKUP(2,1/( has a lookup value of "2", because there were never any explanations where I've seen it.

Thanks. This forum has helped me many times before when I have needed to find a formula. Unfortunately, since I couldn't find this one, I needed to create a new thread.