+ Reply to Thread
Results 1 to 7 of 7

find row based on multiple criteria

  1. #1
    Registered User
    Join Date
    03-21-2012
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2013
    Posts
    15

    find row based on multiple criteria

    in sheet bm1:
    i have to write "ptc" in H column and "1" in I column if sheet bm2 columns (G,R,S,T) contains row with same values like C,E,F,G columns.

    for example row 6 in bm1 matches row 12 in bm2

    example.xlsx
    Last edited by pugulis; 01-11-2016 at 04:03 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,519

    Re: find row based on multiple criteria

    In Bm1 column A

    =C2&E2&F2&G2

    In Bm2 column A

    =G2&R2&S2&T2

    in Bm1 column H


    =IFERROR(IF(VLOOKUP(A2,'bm2'!A2:A170,1,0)+0,"pct",""),"")

    in Bm1 column I

    =IF(H2<>"",1,"")

    Copy all formulas down columns

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    29,925

    Re: find row based on multiple criteria

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    03-21-2012
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: find row based on multiple criteria

    Thank you very much!

    Don't you need to put A2:A170 in dollar signs here?
    =IFERROR(IF(VLOOKUP(A2,'bm2'!$A$2:$A$170,1,0)+0,"pct",""),"")

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    29,925

    Re: find row based on multiple criteria

    Yes. Have you tried the other formula offered?

  6. #6
    Registered User
    Join Date
    03-21-2012
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: find row based on multiple criteria

    Yeah, tried both variants, both work perfectly well!
    File size in 1st variant with 9000 rows - 13.5mb
    File size in 1st variant with 9000 rows - 14.5mb

    Yours is more easy to use but uses more memory. Thanks guys! You are genius!

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    29,925

    Re: find row based on multiple criteria

    You're welcome. Thanks for the rep.


    It would probably be smaller if you changed the full column references to defined ranges, for example, $G$2:$G$? instead of $G:$G


    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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