Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array Formula (2002 SP3)

    I keep messing up the array formula on this spreadsheet. On Tab 1, I would like the information corresponding from Tab 2, Column D to appear in Tab 1, Column D if the information in Tab 1, Columns A and B match the information in Tab 2, Columns A and B. I deleted my messed up formula so it's a clean copy...

    Thanks.
    Pooja
    Attached Files Attached Files

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

    Re: Array Formula (2002 SP3)

    Enter the following array formula (confirm with Ctrl+Shift+Enter) in D2:
    <code>
    =IF(ISNA(MATCH(A2&B2,'Tab 2'!$A$2:$A$166&'Tab 2'!$B$2:$B$166,0)),"",INDEX('Tab 2'!$D$2:$D$166,MATCH(A2&B2,'Tab 2'!$A$2:$A$166&'Tab 2'!$B$2:$B$166,0)))
    </code>
    and fill down. Alternatively, use this normal (non-array) formula
    <code>
    =SUMPRODUCT(('Tab 2'!$A$2:$A$166=A2)*('Tab 2'!$B$2:$B$166=B2)*'Tab 2'!$D$2:$D$166)
    </code>
    and hide zeros.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula (2002 SP3)

    Thank YOU! I used the non-array and it worked perfectly!

    Pooja

Posting Permissions

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