Newbie and use of lookup question?

Discussion in 'Microsoft Excel Misc' started by manasi, Sep 20, 2005.

  1. manasi

    manasi Guest

    I hope I posted this question in the right forum this time.
    I am newbie here and is having hard time in doing this.
    I have an excel workbook which has two sheets Grp1 and Grp2
    within these sheets i have unit and id columns, what I am trying to do
    is find out the combination of unit and id column which is on Grp1 but
    not on Grp2 tab.
    Can I use the Lookup function for this.Could you all please help?


    --
    manasi
    ------------------------------------------------------------------------
    manasi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27411
    View this thread: http://www.excelforum.com/showthread.php?threadid=469228
     
    manasi, Sep 20, 2005
    #1
    1. Advertisements

  2. manasi

    Guest Guest

    I'm not quite sure what you want...so here it goes my guess. I would import
    both lists into MS Access as two separate tables. Use a Left Join and run a
    query based on the information that you want to extract. The Lookup feature
    will simply say if cell A1 = Red and B1 = Apple, then you enter a lookup
    formula in another cell, we'll say D1, that says if C1 = Red, then D1 will
    automatically pop-up with Apple. (confused?).

    "manasi" wrote:

    >
    > I hope I posted this question in the right forum this time.
    > I am newbie here and is having hard time in doing this.
    > I have an excel workbook which has two sheets Grp1 and Grp2
    > within these sheets i have unit and id columns, what I am trying to do
    > is find out the combination of unit and id column which is on Grp1 but
    > not on Grp2 tab.
    > Can I use the Lookup function for this.Could you all please help?
    >
    >
    > --
    > manasi
    > ------------------------------------------------------------------------
    > manasi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27411
    > View this thread: http://www.excelforum.com/showthread.php?threadid=469228
    >
    >
     
    Guest, Sep 20, 2005
    #2
    1. Advertisements

  3. I used column A to hold the units in both sheets.
    I used column B to hold the ID in both sheets.

    Then I insert a new column in Grp1.

    I used this formula:
    =ISNUMBER(MATCH(1,(GRP2!$A$1:$A$20=A1)*(GRP2!$B$1:$B$20=B1),0))

    This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
    correctly, excel will wrap curly brackets {} around your formula. (don't type
    them yourself.)

    Make the range big enough to match your data, but don't use the whole column.

    And drag down the column.

    If you see True, then that pair is on Grp2. False means that pair isn't on
    Grp2.



    manasi wrote:
    >
    > I hope I posted this question in the right forum this time.
    > I am newbie here and is having hard time in doing this.
    > I have an excel workbook which has two sheets Grp1 and Grp2
    > within these sheets i have unit and id columns, what I am trying to do
    > is find out the combination of unit and id column which is on Grp1 but
    > not on Grp2 tab.
    > Can I use the Lookup function for this.Could you all please help?
    >
    > --
    > manasi
    > ------------------------------------------------------------------------
    > manasi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27411
    > View this thread: http://www.excelforum.com/showthread.php?threadid=469228


    --

    Dave Peterson
     
    Dave Peterson, Sep 20, 2005
    #3
  4. manasi

    manasi Guest


    > I used this formula:
    > =ISNUMBER(MATCH(1,(GRP2!$A$1:$A$20=A1)*(GRP2!$B$1: $B$20=B1),0))


    Thanks Dave
    I was able to copy the formula but the results were not the ones I
    wanted I will try giving an example.
    in Grp1 tab I have a combination of
    unit id
    107 236
    100 336
    ......
    when I go to Grp2 tab I have
    unit id
    107 236
    108 456
    .....
    The combination of grp1 100/336 is not on Grp2 therefore I need to show
    that.
    Hope this will clarify


    --
    manasi
    ------------------------------------------------------------------------
    manasi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27411
    View this thread: http://www.excelforum.com/showthread.php?threadid=469228
     
    manasi, Sep 20, 2005
    #4
  5. Did you get a result of True for the 107/236 row?
    Did you get a result of False for the 100/336 row?

    If you did and just need to see the rows with falses, you could apply
    Data|Filter|autofilter to that column.

    Display just the False rows.

    If you need to copy those visible rows somewhere else, you could just select
    those visible rows and edit|copy, then edit|paste.

    ===
    What did those formulas evaluate to?


    manasi wrote:
    >
    > > I used this formula:
    > > =ISNUMBER(MATCH(1,(GRP2!$A$1:$A$20=A1)*(GRP2!$B$1: $B$20=B1),0))

    >
    > Thanks Dave
    > I was able to copy the formula but the results were not the ones I
    > wanted I will try giving an example.
    > in Grp1 tab I have a combination of
    > unit id
    > 107 236
    > 100 336
    > .....
    > when I go to Grp2 tab I have
    > unit id
    > 107 236
    > 108 456
    > ....
    > The combination of grp1 100/336 is not on Grp2 therefore I need to show
    > that.
    > Hope this will clarify
    >
    > --
    > manasi
    > ------------------------------------------------------------------------
    > manasi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27411
    > View this thread: http://www.excelforum.com/showthread.php?threadid=469228


    --

    Dave Peterson
     
    Dave Peterson, Sep 21, 2005
    #5
  6. manasi

    manasi Guest

    Some of the combinations which existed in Grp1 and also Grp2 were
    showing up as False.

    For ex: in row 261 i had 51/63
    going to Grp2 row 265 had 51/63 but the formula evaluated to False on
    Grp1 which meant there is no combination on Grp2 which is not true.
    Please suggest me.


    --
    manasi
    ------------------------------------------------------------------------
    manasi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27411
    View this thread: http://www.excelforum.com/showthread.php?threadid=469228
     
    manasi, Sep 21, 2005
    #6
  7. Find a couple of empty cells.

    Put this in one:
    =grp1!a261=grp2!a265

    and put this in the other:
    =grp1!b261=grp2!b265

    Do you get Trues for both of these?

    If you don't look for extra spaces, numbers treated as text in the offending
    cells.

    manasi wrote:
    >
    > Some of the combinations which existed in Grp1 and also Grp2 were
    > showing up as False.
    >
    > For ex: in row 261 i had 51/63
    > going to Grp2 row 265 had 51/63 but the formula evaluated to False on
    > Grp1 which meant there is no combination on Grp2 which is not true.
    > Please suggest me.
    >
    > --
    > manasi
    > ------------------------------------------------------------------------
    > manasi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27411
    > View this thread: http://www.excelforum.com/showthread.php?threadid=469228


    --

    Dave Peterson
     
    Dave Peterson, Sep 21, 2005
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. HELP ME

    Lookup - V Lookup

    HELP ME, Dec 24, 2003, in forum: Microsoft Excel Misc
    Replies:
    12
    Views:
    395
    Bob Phillips
    Dec 25, 2003
  2. Guest
    Replies:
    1
    Views:
    421
  3. Guest

    Lookup using more than one Lookup value

    Guest, Feb 14, 2005, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    241
    RagDyeR
    Feb 14, 2005
  4. Guest
    Replies:
    0
    Views:
    514
    Guest
    Aug 2, 2005
  5. melchman

    LOOKUP() misses on last lookup

    melchman, Mar 28, 2006, in forum: Microsoft Excel Misc
    Replies:
    4
    Views:
    150
    davesexcel
    Apr 7, 2006
Loading...

Share This Page