+ Reply to Thread
Results 1 to 5 of 5

Index and Match issues

Hybrid View

Guest Index and Match issues 05-19-2005, 12:06 PM
spog00 Just to clarify, What... 05-19-2005, 12:35 PM
Guest RE: Index and Match issues 05-19-2005, 02:06 PM
Guest RE: Index and Match issues 05-19-2005, 02:06 PM
Guest RE: Index and Match issues 05-19-2005, 04:06 PM
  1. #1
    Mo
    Guest

    Index and Match issues

    I am having trouble with a index and match function.

    My data on first tab (sheet 1) has the following:
    A 1 f
    B 2 g
    C 3 h

    I want to add a fourth column that is pulled from the second tab (sheet 2).
    The data on the second tab
    A 1 f 2 $100,000
    c 3 h 17 $410,000

    Note the first sheet has all of the possible categories (65 different ones).
    The second sheet only has catagories where the count and $ are populated (35
    of total possible 65). I have tried the index and match, but am having
    trouble because I have three variables to look up.

    Any thoughts? thanks

  2. #2
    Registered User
    Join Date
    02-21-2005
    Posts
    56
    Just to clarify, What specifically are you looking up, and what data do you want returned?

    would searching for A pull up a uniquevalue, or searching for A and f or does it have to be all three?

    Alastair

  3. #3
    bj
    Guest

    RE: Index and Match issues

    Sumproduct will probably work
    in your 4th column enter
    =sumproduct(--(sheet2!$a$1:$a$100=a1),--(sheet2!$b$1:$b$100=b1),--($C$1:C100=C1),sheet2!$D$1:$D$100)
    for the count and
    =sumproduct(--(sheet2!$a$1:$a$100=a1),--(sheet2!$b$1:$b$100=b1),--($C$1:C100=C1),sheet2!$E$1:$E$100)
    Change the columns as necessary for your workbook.
    the "--(" changes the logical true false to 1,0

    "Mo" wrote:

    > I am having trouble with a index and match function.
    >
    > My data on first tab (sheet 1) has the following:
    > A 1 f
    > B 2 g
    > C 3 h
    >
    > I want to add a fourth column that is pulled from the second tab (sheet 2).
    > The data on the second tab
    > A 1 f 2 $100,000
    > c 3 h 17 $410,000
    >
    > Note the first sheet has all of the possible categories (65 different ones).
    > The second sheet only has catagories where the count and $ are populated (35
    > of total possible 65). I have tried the index and match, but am having
    > trouble because I have three variables to look up.
    >
    > Any thoughts? thanks


  4. #4
    Peo Sjoblom
    Guest

    RE: Index and Match issues

    One way, assume your data in sheet 1 starts in A2 with "A", then 1 in B2 and
    so on,
    in sheet2 your data starts in A2 as well, in D2 sheet1 put

    =INDEX(Sheet2!$D$2:$D$10,MATCH(1,(Sheet2!$A$2:$A$10=A2)*(Sheet2!$B$2:$B$10=B2)*(Sheet2!$C$2:$C$10=C2),0))


    entered with ctrl + shift & enter

    and copy down


    Regards,

    Peo Sjoblom

    "Mo" wrote:

    > I am having trouble with a index and match function.
    >
    > My data on first tab (sheet 1) has the following:
    > A 1 f
    > B 2 g
    > C 3 h
    >
    > I want to add a fourth column that is pulled from the second tab (sheet 2).
    > The data on the second tab
    > A 1 f 2 $100,000
    > c 3 h 17 $410,000
    >
    > Note the first sheet has all of the possible categories (65 different ones).
    > The second sheet only has catagories where the count and $ are populated (35
    > of total possible 65). I have tried the index and match, but am having
    > trouble because I have three variables to look up.
    >
    > Any thoughts? thanks


  5. #5
    Mo
    Guest

    RE: Index and Match issues

    Now I am getting #N/A in the cell, any ideas? thanks

    "Peo Sjoblom" wrote:

    > One way, assume your data in sheet 1 starts in A2 with "A", then 1 in B2 and
    > so on,
    > in sheet2 your data starts in A2 as well, in D2 sheet1 put
    >
    > =INDEX(Sheet2!$D$2:$D$10,MATCH(1,(Sheet2!$A$2:$A$10=A2)*(Sheet2!$B$2:$B$10=B2)*(Sheet2!$C$2:$C$10=C2),0))
    >
    >
    > entered with ctrl + shift & enter
    >
    > and copy down
    >
    >
    > Regards,
    >
    > Peo Sjoblom
    >
    > "Mo" wrote:
    >
    > > I am having trouble with a index and match function.
    > >
    > > My data on first tab (sheet 1) has the following:
    > > A 1 f
    > > B 2 g
    > > C 3 h
    > >
    > > I want to add a fourth column that is pulled from the second tab (sheet 2).
    > > The data on the second tab
    > > A 1 f 2 $100,000
    > > c 3 h 17 $410,000
    > >
    > > Note the first sheet has all of the possible categories (65 different ones).
    > > The second sheet only has catagories where the count and $ are populated (35
    > > of total possible 65). I have tried the index and match, but am having
    > > trouble because I have three variables to look up.
    > >
    > > Any thoughts? thanks


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1