getting second to last values in a lookup with multiple values

**amartino44** · 06-11-2013, 06:27 PM

Sure, although every time someone asks for data, nobody else ever responds lol, but here it goes. This is the new formula =LOOKUP(2,1/(B1:B8=E3),C2:C8) and it finds the corresponding value to the final value of Bob in column B. NOW, I want to use the same formula but modify it to find the NEXT TO LAST value of Bob in column B. The spreadsheet I have has tons of values for Bob, so even though in this spreadsheet it is the first value, I need the formula to find the value of Bob right before the last value of Bob. Can you do it!!????

**Tony Valko** · 06-11-2013, 07:16 PM

Try this array formula**:

=INDEX(C:C,LARGE(IF(B2:B8=E3,ROW(B2:B8)),N))

Where N = the number of the instance you want to find from the bottom to the top.

If N=1 then the formula will find the bottom-most instance. If N=2 then the formula will find the 2nd instance from the bottom.

If you're wanting to find ALL instances then it's easier to look from the top down.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

getting second to last values in a lookup with multiple values

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: getting second to last values in a lookup with multiple values

Re: getting second to last values in a lookup with multiple values

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions