Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Index...Match (Excel 2003)

    Hi

    Given the following data in sheet1 and using

    =INDEX(Sheet1!$C$2:$C$100,INDEX(MATCH(B2&C2,Sheet1 !$A$2:$A$100&Sheet1!$C$2:$C$100,0),0))
    I am unable to get the expected result as in sheet 2

    First Name ..................... Last Name...................Classification
    Bloggs.............................Joe............ ......................Management
    Jones...............................Fred.......... ......................Engineer

    In sheet2, I get after placing the above formula in C2 :

    First Name.....Last Name........Classification
    Bloggs........Joe........................0
    Jones..........Fred......................0

    I should get the Classification right as above but I don't. I beleive that I have missed the some parameters in the formula but unable to find it.

    TIA, fy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Index...Match (Excel 2003)

    If both sheet 1 and Sheet2 are the same with first name in A nd last name in B, then in C2 of Sheet2 I think you need:

    =INDEX(Sheet1!$C$2:$C$100,INDEX(MATCH(A2&B2,Sheet1 !$A$2:$A$100&Sheet1!$B$2:$B$100,0),0))

    As you have it written it contains a circular reference (C2 refers to C2)

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index...Match (Excel 2003)

    Thanks, Steve. Got it.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

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