Would someone please explain in a simple way when to use an array? Is it for every index match formula every time?

like
Posting as :
works at
You are currently posting as works at

Array formulas are a powerful, and partly legacy tool. A simple example is the frequency or sumproduct formulas, still useful. Array formula still allow to make much more complicated types of conditional sum and count (among other formulas that support arrays), but their usefulness is now relegated to more complex situations after the introduction of for instance sumifs and countifs (when those did not exist, array formulas were the only way to do that)

like

You must feel old now...

Nooooo

funny

Arrays are for when you want to run a function on a group of data simultaneously eg. Count the number of characters in 10 cells with one formula.

Anecdotally you want to avoid them because they are fiddly and most people don't know how they work. If an array is the only way to do your calculation then Excel is the wrong tool for the job.

Don’t forget the squiggly bracket complication

I think OP might just mean the array parameter in functions like index. That’s just a range of cells for the function to scan through.

Array formulas are complex uses of excel functions and if you are learning index/match then don’t look at them yet

Related Posts

Wipro I had joined Wipro on July 21,2021.
In middle of my Java training, they moved me to Salesforce developer training, than I completed my Salesforce PD-1 certification on September 5.
After that they tagged me to Project as Mulesoft Developer -1.But due to incorrect tagging,They didn't give me any work for 3 months and after that they removed me from project on March 7,2022.
In last quarter I was in free pool, Now they moved me out of Salesforce to Cloud Transformation.
Pls help

like

When you knew you were right but just wanted to humble your senior

like

My counselor is an ass. She has my rating but refuses to release it😖!

like

Is it worth pursuing the Cpa if you know you’re not staying in accounting?

like

What is day to day work for a lead consultant at TW?

Is it super busy like so many calls with clients (post office hours or even on weekends)?

like

Anyone living in Pune Hinjewadi area, need help regarding flat.
Can u please tell me society names which are good and have rent around 15k for 2 bhk

like

Can you please like this post so that I can DM for referrals. Thanks in advance 😊

like

Thoughts on work-life balance at 21Grams vs at Area 23?

like

What happened in the Avalon West Hollywood apartment shooting? Was it somebody the woman knew or somebody broke in and shot her in the hallway? I looked for news reports but unsure

like

Got an offer at a boutique firm, they are offering mid-100k (3 YOE, but I’m retooling). Great opportunity (like, I really love everything about it), and I’m prepared to take a pay cut, but the salary seems especially low. Any insight into what talent-side boutiques are offering in high COL areas (LA, NY)?

likehelpful

Anyone hear of relationship OCD? I think I suffer from it. Strategies to cope?

like

Compensation thread-Firm, years of experience (in consulting and industry), salary

Anyone heard of AQR capital management, Bangalore?
Whats your thoughts on this company. Glassdoor reviews are mostly from outside India.

like

I love mondays because... (sarcastic answers only)

like

Does anyone here work in forensic investigations and disputes at KPMG? I am looking for help with a job application

like

Any hiring managers or senior managers from the Big4 (or any other firm with a forensic department that handles investigations or claims services like EY’s I&C or IFCS) that would be open to reviewing my resume and providing any suggestions before I start submitting applications? I have a government and IT background which will allow me to hit the ground running in any new role.

likehelpful

I got Chuba off the waivers. Would you trade him to the owner of CMC for someone like Marvin Jones Jr?

like

Googlers, I’ve heard a rumor you can submit a ticket to REWS, to get a time slot and go in and clean out your desk. Anyone else know about this? Is it just a rumor? 😭 NYC based if it helps

like

Looking for salaries for middle management in marketing.

like

Can anyone tell me the Manager bandings for S&C?

like

Additional Posts in Excel Genius

Q. Is it possible to turn off track changes on a shared excel document ? I looked it up and it saying, “Press review” and I can disable from “Track Changes.” However on the document I’m using, I press review and I only have the option to “show changes.” Anyone know a way around this. Trying to at least remove the history due to a micromanaging manager

like

Alright. I am very much so not an Excel genius; I have the Excel skills of your 90 year old grandma who just owns a Jitterbug. Im semi-tech savvy (young millennial), but I’m new to corporate after transitioning into my role from the classroom. Among some skills I know I need, understanding basic Excel functionality is the most immediate. Does anyone have any recommendations for a very novice look into Excel? Thank you in advance!

like

EY trying to move away from Excel . Think it’s possible? Want to see it happen?

like

I love this bowl. Sorry, continue to share nerdy excel shortcuts which I loooovee.

like

Hi I need help please! Trying to sumifs with one of the criteria being a blank cell. Problem is the cell is not truly blank as it contains a formula that is producing the blank cell. How can I get around this to get my proper summed results???

like

Help!! Time-sensitive:
Please tell me how I can achieve this:

To create concatenation of every possible combination of Type to Price for every ID.
I provided an example below with the outcome I want in the second table. Would prefer if this be down without a macro.

Post Photo
like

Been in consulting for over a year and wondering what other formulas other than index/match and vmatch is useful.

Is it possible to make a VBA userform that let's me interact with the workbook? I know how to create the form but default seems to be that I can't do anything else in the workbook until the form is closed.

like

I know this is for excel, but any Tableau experts here can let me know how to present my mixed (countries, states, provinces) data on a map. I’ve tried dual axis but still not where I want it to be.

like

Can anyone recommend good learning resources for excel? I’ve used it for years and am familiar with all the vlookups & index matches but would like to grow my skills beyond that... TIA

like

Index Match > Vlookup

like

Does anyone know the best approach when dealing with Ad sales linear and digital in Analytics? Or any knowledge about the ad sales industry

like

Can anyone provide a reference to how to efficiently create and link an excel spreadsheet into a ppt (and make it dynamic). Thanks in advance!

like

Trying to create a crosswalk between two sets of text data with non-exact names. For instance:

Name: ABC Family Medicine
Benchmark Specialty: Family Medicine

The format of the name isn't always in the same order (isn't always preceded by "ABC")

Ideas?

like

I have a 30 minute excel study case as the final step for a potential job. It would be a great raise and a step into the field I ultimately want to be in. The hiring manager told me that it is a basic test, more to see how much I know. However, I want to exceed expectations. I do use excel daily, but nothing too complicated. Does anyone have any tips or resources that might help me wow upper management and land this role? Thank you in advance.

likehelpful

Is there an option to permanently turn off the research function in excel without using external add-on/plug-ins? Not sure why it exists in the first place...

like

Problem:
There is a table. Column A:D are mapped to column E with unique records. In a new column (Q), there is a list of values that that contain any randomized value from Columns A:D. What is the syntax to map Column Q to column E?

Is using INDEX/MATCH the right direction?

like

How would I create a bar graph that shows same store sales growth for 5 products that were promoted during different time periods?

like

Anyone ever dealt with grouped rows / columns inexplicably stopping working? I have a big and pretty important model which uses grouped rows a lot (to click the plus and pop out more detail), and yesterday I opened it and it’s just all hidden instead of grouped. Any ideas how to fix?

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Scan your QR code to download
Fishbowl app on your mobile

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal