Excel Magic Trick 984: Lookup Penultimate: Get Second To Last Item From Column Of Mixed Data

[@osplgurugram7080]

Genius - i was struck in this from long time . Thanks Dear .. 😇

[@excelisfun]

You are welcome, krn14242!!

[@excelisfun]

You are welcome!

[@markreuber5197]

No bumps...just am always amazed at the logical approach in combining numerous functions with successful resolves. Great job!

[@excelisfun]

Happy New Year to you! I'll keep making them!

[@excelisfun]

Cool!

[@excelisfun]

I am not sure, I got asked this question and so I tried to figure it out. Over my Excel life, it never ceases to amaze me what people need Excel to do! Anyone else know of a penultimate lookup situation?

[@excelisfun]

No, why do you ask? Is there a bump?

[@excelisfun]

I meant on my head! :) Yes, Excel is a lot of fun and it always amazes me also at what Excel can do!

[@krn14242]

I need to start using aggregate more I think. Thanks Mike, great job as usual.

[@ohdjrp4]

Hi Mike Happy New Year!! Thanks again!

[@Esther1242]

thank you so much, you are so great they way explain is easy to understand. once again thank.

[@elphau]

Happy New year to you!! Keep making these awesome videos!!

[@mayadaaref2728]

yes.. you are really awesome... ur videos like heroes to me in my work ^_-

[@smile4kiran]

what if i need penultimate from a row? as you explained can you provide for a row?

[@markreuber5197]

does your head hurt?

[@jhammer1014]

Holy cow... never knew the depth of functionality available. What would be a real world purpose to get to a penultimate, just out of curiosity.

[@cyyerica]

How about getting the second to last NUMBER in mixed data? e.g. 1,a,2,b,3,c,4,d The second to last number I wanna lookup should be "3" in this case. Please help!!!

[@cyyerica]

Oh. Forgot to mentioned, there are blank cells in between those data as well. E.g. 1,a, , ,2,b, , ,3,c, , , , ,4,d

[@123edczaq]

Here is another way to do this with no INDEX involved! :) P.S. I had to replace "not equal" signs in the formula with "#" because otherwise it wouldn't allow me to post this comment (HTML tag error)... Before entering the formula you have to change those "#" back to "not equal" signs. =LOOKUP(2,1/(E6:INDIRECT(ADDRESS(MATCH(LOOKUP(2,1/(E6:E16#""),E6:E16),E6:E16,1)+ROW(E6)-2,COLUMN(E6)))#""),E6:INDIRECT(ADDRESS(MATCH(LOOKUP(2,1/(E6:E16#""),E6:E16),E6:E16,1)+ROW(E6)-2,COLUMN(E6))))

[@excelisfun]

You are welcome!