Using Lookup with a Two Column Lookup Range

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.

Originally Posted by protonLeah

Do columns B and C both have multiple occurrences of the value in B34 and you want the one that's furthest down in either column?

This. I want the furthest down, (or in other words, first previous) occurance of B34 in either B or C. Then I want to lookup a corresponding value 10 columns over from that cell.

It was kind of hard to explain, so thanks for clarifying. Your second question is basically about me just trying to reiterate what I had said earlier.

I will attach a sample worksheet when I get on my main computer.

Thanks

Workbook1.xlsx

Here is a sample workbook. One cell uses my current formula, and I left a blank spot open for the new formula.

bump no solution

Thanks for your formula. I was able to take pieces of it and plug it in to my old formula, and it works great.

This is my new formula, thanks to your help, for G2:

Formula:

Please Login or Register  to view this content.