Log In
Register
Home
Formula to show filtered item in cell
TimC
West Midlands, UK
Member
Members
Forum Posts: 32
Member Since:
June 27, 2016
June 27, 2016
Offline1
March 6, 2018 - 8:15 pm
Hello,
I have a filtered block of data, and I filter on items in column L, say. I've picked up a formula from an Excel website, which shows in a cell which item in that column I'm filtering on :
{=INDEX(L2:L10000,MIN(IF(SUBTOTAL(3,OFFSET(L2,ROW(L2:L10000)-ROW(L2),0)),ROW(L2:L10000)-ROW(L2)+1)))}
It works fine, but I'd be grateful, if someone could explain to me in simple terms, what the formula actually does - I particularly lose the plot when it comes to the array brackets.
Thanks
TimC
Mynda Treacy
Admin
Forum Posts: 4782
Member Since:
July 16, 2010
July 16, 2010
Offline2
March 11, 2018 - 9:12 pm
Hi Tim,
The curly braces are inserted by Excel automatically when you press CTRL+SHIFT+ENTER to complete the formula.
This tutorial on arrays might help you understand how your formula is evaluating: https://www.myonlinetraininghu.....ay-formula
Mynda
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Riny van Eekelen
Guest(s) 5
Currently Browsing this Page:
1 Guest(s)
1 Guest(s)
Top Posters:
Catalin Bombea: 1944
SunnyKow: 1432
Anders Sehlstedt: 956
Purfleet: 415
Hans Hallebeek: 377
Frans Visser: 351
David_Ng: 318
lea cohen: 248
Jessica Stewart: 219
A.Maurizio: 216
Newest Members:
Waqas Arshad
Techia Braveboy
Charles Buchanan
Tiago Braga
Rehan Baig
Fred HORVATH
Silke Schmidt
Stuart Barr
Amelia Nolte
David Wier
Forum Stats:
Groups: 3
Forums: 25
Topics: 7157
Posts: 31386
Member Stats:
Guest Posters: 50
Members: 34656
Moderators: 2
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Jessica
Moderators: Velouria, Riny van Eekelen
© Simple:Press —