Array Formulas not necessary
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Array Formulas not necessary
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Array Formulas not necessary

    I was asked to help my boyfriend on a vlookup with multiple conditions. I know that requires using Index Match and using them as an array formula. However when he went to hit "enter" he didn't want to use "ctrl, shift, enter" as I told him but it still ended up working. At work today I checked another formula to turn back the second value from a vlookup instead of the first, something I also know needs array formulas. And again, the ctrl shift enter wasn't necessary.

    The formulas in question are below

    =index(A:A,match(1,(B:B=F2)*(C:C=G2),0))
    =INDEX(Invoice!D:D,SMALL(IF(Invoice!F:F=B2,ROW(Invoice!F:F)),2))

    Does anyone know why it is working without telling excel it is an array formula?

  2. #2
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,024
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array Formulas not necessary

    Welcome to the forum!

    Because only one cell was selected so one result was returned?

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,554
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Array Formulas not necessary

    All i can tell you is neither of those would work on my version without CTRL-SHIFT-ENTER. Are you sure you dont have an INDEX in there somewhere?
    Looking for opportunities

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,861
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Array Formulas not necessary

    Are you both on Office 365?
    If so, are you on the insiders program?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array Formulas not necessary

    Quote Originally Posted by Kenneth Hobson View Post
    Welcome to the forum!

    Because only one cell was selected so one result was returned?
    But, for example, the first one had (B:B=F2)*(C:C=G2) that portion of the formula should have turned back an array which means Excel needs to be told, it always had but not today...

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array Formulas not necessary

    As far as I know, no... but I am not sure what that means.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,861
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Array Formulas not necessary

    What version of Excel are you both running?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array Formulas not necessary

    I am using office 365, but after some research don't think I am on the insiders program.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,861
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Array Formulas not necessary

    Ok, if you click on file > Account > where it says "About Excel" what does the last line of that section say? Probably something like Monthly Channel, or Monthly (targeted) channel.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array Formulas not necessary

    Quote Originally Posted by Fluff View Post
    Ok, if you click on file > Account > where it says "About Excel" what does the last line of that section say? Probably something like Monthly Channel, or Monthly (targeted) channel.
    "Semi-annual Channel" why, what does that mean?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •