Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2005
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    multiple look up (excel 2003)

    i would like to create a look up on more than one column ie I have a pivot table showing budgets by county and by school. I would like my look up to first find the county and then the school and give me the corresponding figure - see below
    essex school A

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

    Re: multiple look up (excel 2003)

    Say that your pivot table starts in cell F1, and that you enter the name of a county in cell P1 and that of a school in cell P2. The following formula will retrieve the budget for that county and school:

    =GETPIVOTDATA("Budget",$F$1,"County",P1,"School",P 2)

  3. #3
    New Lounger
    Join Date
    Sep 2005
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple look up (excel 2003)

    If the data was in a table rather than a pivot, is there another way of getting the same result?
    many thanks

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

    Re: multiple look up (excel 2003)

    Do you only want to do a lookup, or can there be multiple records (rows) for a county and school, and do you want to sum the budget amounts?

  5. #5
    New Lounger
    Join Date
    Sep 2005
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple look up (excel 2003)

    Multiple records - it will be in this kind of format
    subject 1 subject 2 etc
    county 1 school 1

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

    Re: multiple look up (excel 2003)

    Let's say the data are in A1:C100 (counties in column A, schools in column B, budget amounts in column C).
    The county to look for is in P1, and the school to look up in P2.

    You can use the following array formula (confirmed with Ctrl+Shift+Enter instead of just enter):

    =INDEX($C$1:$C$100,MATCH(P1&P2,$A$1:$A$100&$B$1:$B $100,0))

    or this 'normal' formula:

    =SUMPRODUCT(($A$1:$A$100=P1)*($B$1:$B$100=P2)*$C$1 :$C$100)

Posting Permissions

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