Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array Formula - IF (2002)

    This array formula only works on the first value in a list (A26) and returns a N/A for all others. I am doing cntrl+shift+enter prior to entering. Can someone advise on what I am doing wrong???

    Thanks.

    =IF(A26=$D$5:$D$19, D26/F5, "N/A")

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Array Formula - IF (2002)

    You only provide A26, not a list of values, so the result is to be expected. Can you explain what you want to accomplish? Please try to be clear and exact.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula - IF (2002)

    Hans, I want the formula to look in A26 for this value and if there is a match in the list d5 thru d19, then I want the division to take place between the adjacent columns. For example, if there is a match in d5 then I want to do the following division in the cell I have this formula d26/f5.

    List one is comprised of: A2655
    List two is comprised of: d4:f19

    Does this make sense?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Array Formula - IF (2002)

    Not really, I'm afraid. I'll take a guess: try the following standard (non-array) formula:

    =D26/VLOOKUP(A26,$D$5:$F$19,3,FALSE)

    and fill down as far as needed.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula - IF (2002)

    Hans, thanks. This gives me the desired result. Was this also possible w/an array.

    I am trying to better understand arrays...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Array Formula - IF (2002)

    Not with VLOOKUP, but you could use INDEX and MATCH:
    - Select A26:A55.
    - Enter the formula
    <code>
    =D2655/INDEX(F5:F19,MATCH(A26:A55,D519,0))
    </code>
    - Confirm with Ctrl+Shift+Enter to make it an array formula.

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Array Formula - IF (2002)

    We use quite a few array formula setups in place of pivot table type layouts.
    try this, where column B3 down contains the alphabet, column C3 down contains values, D3 is my lookup letter, E3 is my array formula cell, F3 is my divisor
    <pre>{=SUM(IF(D3=$B$3:$B$14,$C$3:$C$14/$F$3))}</pre>



    Alan

Posting Permissions

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