Formula with INDEX and IF
Results 1 to 5 of 5

Thread: Formula with INDEX and IF
Thanks Thanks: 0 Likes Likes: 0

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

    Default Formula with INDEX and IF

    I have a fairly simple formula that I am trying to amend so that inserting a line does not cause the cell values to change using the INDEX command. The formula I was using was:

    =IF(E6>0, E6, 0)

    Which I have updated to:

    =IF(INDEX(C:E,3,3>0),INDEX(C:E,3,3,0))

    This produces a #VALUE error so I am obviously have the formatting wrong but cant get my head around what to change, can anyone help?

  2. #2
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    576
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Formula with INDEX and IF

    That IF INDEX combination is always true so you will always return INDEX(C:E,3,3) I.e. the 3rd row and 3rd column of C:E (E3)

    It is nested incorrectly hence the error,

    It would look like:
    =IF(INDEX(C:E,3,3>0),INDEX(C:E,3,3),0)

    Though this looks a bit weird to me!!
    √-1 2³ ∑ π
    …And it was delicious!

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,330
    Post Thanks / Like
    Mentioned
    439 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Formula with INDEX and IF

    If you want E6 it should be
    =IF(INDEX(C:E,6,3)>0,INDEX(C:E,6,3),0)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,031
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula with INDEX and IF

    I think it should look more like:

    =IF(INDEX(C:E,6,3)>0,INDEX(C:E,6,3),0)

    Change the 6 to 3 if you really meant to say E3 in your description.
    [code]your code[/code]

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

    Default Re: Formula with INDEX and IF

    Whats behind the decision to use index thats just pointing at a fixed cell?

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
  •