Results 1 to 6 of 6

Thread: Lookup? (2002)

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

    Lookup? (2002)

    Worksheet 1 - I have a pivot table that is summarizing spend by department. In column A it has department number and in column B it has class code. In column c it has acutal spend.

    Worksheet 2 - On another spreadsheet, I have department numbers in the individual columns beginning in row C through AA and in column B I have expense class code per above. In the rows below each department number the actual spend is listed.

    I would like to be able to, using the department number on worksheet 2, pull the actual spend by class code into column C on worksheet 1.

    I will attach a sample.

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

    Re: Lookup? (2002)

    In D2, enter the formula

    =IF(ISNA(INDEX(Worksheet2!$C$2:$D$3,MATCH(B2,Works heet2!$A$2:$A$3,0),MATCH(A2,Worksheet2!$C$1:$D$1,0 ))),"",INDEX(Worksheet2!$C$2:$D$3,MATCH(B2,Workshe et2!$A$2:$A$3,0),MATCH(A2,Worksheet2!$C$1:$D$1,0)) )

    Fill down as far as needed.

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

    Re: Lookup? (2002)

    Hans, I have attached the actual worksheet. I tried the formula with no luck.

    Can you help?

    MJ

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Lookup? (2002)

    Does the attached do what you want? I have adjusted the range references accordingly and also converted the values in column A on Worksheet1 from text to numbers.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Lookup? (2002)

    Works great. I multiplied all number values by 1. Don't understand why this was need but THANKS. Appears to work great. Where could I look to understand how this beautiful formula works?

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

    Re: Lookup? (2002)

    Let's look at the formula in D2:

    =IF(ISNA(INDEX(Worksheet2!$C$2:$AH$52,MATCH(B2,Wor ksheet2!$A$2:$A$52,0),MATCH(A2,Worksheet2!$C$1:$AH $1,0))),"",INDEX(Worksheet2!$C$2:$AH$52,MATCH(B2,W orksheet2!$A$2:$A$52,0),MATCH(A2,Worksheet2!$C$1:$ AH$1,0)))

    The essential part is

    INDEX(Worksheet2!$C$2:$AH$52,MATCH(B2,Worksheet2!$ A$2:$A$52,0),MATCH(A2,Worksheet2!$C$1:$AH$1,0))

    If we indicate this with A, the formula is =IF(ISNA(A),"",A): if A results in #N/A (not available), then return an empty string "", else return the result of A.

    A says: take the range Worksheet2!$C$2:$AH$52, and look up the value in the row indicated by MATCH(B2,Worksheet2!$A$2:$A$52,0) and the column indicated by MATCH(A2,Worksheet2!$C$1:$AH$1,0).

    MATCH(B2,Worksheet2!$A$2:$A$52,0) looks up the value of B2 in Worksheet2!$A$2:$A$52 and returns the index of the first match; the 3rd argument 0 specifies that the match must be exact.
    Similar for MATCH(A2,Worksheet2!$C$1:$AH$1,0).

Posting Permissions

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