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

preview_player
Показать описание

See Two Examples for getting the Second To Last Item From Column Of Mixed Data:
1) INDEX and LARGE functions with an array calculation (Requires Ctrl + Shift + Enter)
2) INDEX and AGGREGATE function with an array calculation (not require Ctrl + Shift + Enter)
  1. excelisfun
  2. Excel 2013
  3. Excel 2003
  4. Excel 2007
  5. Excel 2010
  6. excelisfun
Рекомендации по теме
Excel Magic Trick 0:06:44

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

Excel Magic Trick 0:08:09

Excel Magic Trick 983: Get Last Data For Employee From Across Many Sheets

Excel Magic Trick 0:07:27

Excel Magic Trick 979: 2 Lookup Values in Lookup, Can't Have Helper Column.

Excel Magic Trick 0:03:21

Excel Magic Trick 987: LOOKUP Last Number In Column, Excluding Zeroes

Excel Magic Trick 0:05:42

Excel Magic Trick 985: Filter by Decimal: Helper Column or Advanced Filter w Formula Criteria

Excel Magic Trick 0:07:44

Excel Magic Trick 975: 7 Keystrokes to Enter an Excel Formula, including Array Formulas.

Excel Magic Trick 0:07:35

Excel Magic Trick 994 Min Digit From A Cell MIN or AGGREGATE Array Formula (nowtelugu.com)

Excel Magic Trick 0:08:38

Excel Magic Trick 980: OR and AND Counting Criteria In One Formula, Boolean or MATCH?

Excel Solution - 0:00:20

Excel Solution - How to Find the Second and Third Match record with Vlookup

Excel Magic Trick 0:08:26

Excel Magic Trick 982: Add w One Condition, Use SUMIF Instead of SUMPRODUCT or DSUM

Excel Magic Trick 0:03:42

Excel Magic Trick 988: What is Actual Return on my Art Investment from Antiques Roadshow?

[How to] Lookup 0:06:19

[How to] Lookup a Specific Cell from a Table in Excel

Excel Magic: Mastering 0:13:31

Excel Magic: Mastering VLOOKUP and Nested Formulas for Data Analysis

How to find 0:14:02

How to find last non-blank value - Excel lookup challenge

How To Find 0:05:43

How To Find The Last Number In A Column Of Data In Excel

How to Find 0:07:06

How to Find Last Cell Value & Position in excel

Excel-makroesimerkki 0:04:31

Excel-makroesimerkki

Second to last 0:14:14

Second to last non-blank cell excel

a double xlookup 0:03:27

a double xlookup example

PQB13: PowerQueryBasics: 2-dimension 0:21:40

PQB13: PowerQueryBasics: 2-dimension search using Formulas (Vlookup+Match) & Power Query

Weird trick with 0:00:54

Weird trick with any 3-digit number! #shorts

How To Use 0:08:24

How To Use LOOKUP To Find The Last Value Based On Criteria In Excel

Find 2nd largest 0:04:31

Find 2nd largest and 2nd smallest value in a range in Excel

Excel Ignited Day 0:14:13

Excel Ignited Day 15: Tables, Slicers, VLOOKUP

Комментарии
Автор

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

markreuber
Автор

I meant on my head! :)

Yes, Excel is a lot of fun and it always amazes me also at what Excel can do!

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
Автор

Happy New Year to you!

I'll keep making them!

excelisfun
Автор

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

osplgurugram
Автор

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

krn
Автор

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

elphau
Автор

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

Esther
Автор

Hi Mike Happy New Year!! Thanks again!

ohdjrp
Автор

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

mayadaaref
Автор

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

smilekiran
Автор

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.

jhammer
Автор

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

cyyerica
Автор

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))))

edczaq
Автор

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