Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

Salary Surveys
Property & Casualty, Life, Health & Pension

Health Actuary Jobs
Insurance & Consulting jobs for Students, Associates & Fellows

Actuarial Recruitment
Visit DW Simpson's website for more info.
www.dwsimpson.com/about

Casualty Jobs
Property & Casualty jobs for Students, Associates & Fellows


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 09-01-2019, 08:19 PM
Dan Moore's Avatar
Dan Moore Dan Moore is online now
Member
SOA AAA
 
Join Date: Jan 2008
College: University of Dallas
Posts: 2,776
Blog Entries: 1
Default Excel XLOOKUP function

New Excel function: XLOOKUP

Geek out!

https://www.cnbc.com/2019/09/01/micr...s-rejoice.html
__________________
The best time to plant an oak tree is twenty years ago. The second best time is right now.
Reply With Quote
  #2  
Old 09-01-2019, 10:47 PM
Numerical Neuroticism's Avatar
Numerical Neuroticism Numerical Neuroticism is online now
Member
SOA
 
Join Date: Jan 2011
Studying for golf
Posts: 2,001
Default

Reply With Quote
  #3  
Old 09-01-2019, 11:00 PM
Vorian Atreides's Avatar
Vorian Atreides Vorian Atreides is online now
Wiki/Note Contributor
CAS
 
Join Date: Apr 2005
Location: As far as 3 cups of sugar will take you
Studying for CSPA
College: Hard Knocks
Favorite beer: Most German dark lagers
Posts: 67,196
Default

She seems nice.
__________________
I find your lack of faith disturbing

Why should I worry about dying? Itís not going to happen in my lifetime!


Freedom of speech is not a license to discourtesy

#BLACKMATTERLIVES
Reply With Quote
  #4  
Old 09-02-2019, 02:14 PM
Dr T Non-Fan Dr T Non-Fan is offline
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 98,671
Default

Seems like a solution to user-borne issues.
__________________
"Facebook is a toilet." -- LWTwJO

"45 es un titere" -- Seal of The President of The United States of America protest art
Reply With Quote
  #5  
Old 09-02-2019, 11:54 PM
windows7forever windows7forever is offline
Member
SOA
 
Join Date: Apr 2016
Posts: 412
Default

index match(1, xxx * xxx, 0) is powerful to do multiple matches that vlookup and hlookup cannot do alone. It sounds from the article that xlookup will be more powerful than index/match.
Reply With Quote
  #6  
Old 09-03-2019, 06:16 AM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 91,405
Blog Entries: 6
Default

The problem I'm having with XLOOKUP & other of the new functions is their "spill" functionality -- meaning, the results of the single function call do not necessarily live in one cell.

I don't like spreadsheets with changing structure like that, which is one reason I do not like pivot tables for production.
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #7  
Old 09-03-2019, 06:18 AM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 91,405
Blog Entries: 6
Default

Oh, and now we have a lovely new error type:

https://support.office.com/en-us/art...2-ef9cc9ad4023

Quote:
#SPILL! errors in Excel
Excel for Office 365 Excel for Office 365 for Mac Excel for the web Excel for iPad More...
#SPILL errors are returned when a formula returns multiple results, and Excel cannot return the results to the grid. For more details on these error types, see the following help topics:

Spill range isn't blank

Indeterminate size

Extends beyond the worksheet's edge

Table formula

Out of memory

Spill into merged cells

Unrecognized/Fallback
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #8  
Old 09-03-2019, 06:19 AM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 91,405
Blog Entries: 6
Default

Here's the official Microsoft announcement for XLOOKUP:

https://techcommunity.microsoft.com/...UP/ba-p/811376
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #9  
Old 09-03-2019, 09:26 AM
Vorian Atreides's Avatar
Vorian Atreides Vorian Atreides is online now
Wiki/Note Contributor
CAS
 
Join Date: Apr 2005
Location: As far as 3 cups of sugar will take you
Studying for CSPA
College: Hard Knocks
Favorite beer: Most German dark lagers
Posts: 67,196
Default

Quote:
Originally Posted by campbell View Post
The problem I'm having with XLOOKUP & other of the new functions is their "spill" functionality -- meaning, the results of the single function call do not necessarily live in one cell.

I don't like spreadsheets with changing structure like that, which is one reason I do not like pivot tables for production.
but would replace "liv[ing] in one cell" with "liv[ing] in a block of cells of predetermined dimensions".

I would be interested in seeing what the implementation details of XLOOKUP (i.e., details of how to invoke the function) are.
__________________
I find your lack of faith disturbing

Why should I worry about dying? Itís not going to happen in my lifetime!


Freedom of speech is not a license to discourtesy

#BLACKMATTERLIVES
Reply With Quote
  #10  
Old 09-03-2019, 10:54 AM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 91,405
Blog Entries: 6
Default

Here is the official XLOOKUP help page:
https://support.office.com/en-us/art...rs=en-US&ad=US
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 12:10 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.38599 seconds with 11 queries