# find row based on multiple criteria

1. ## 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

2. ## 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. ## Re: find row based on multiple criteria

Formula:
`Please Login or Register  to view this content.`

Formula:
`Please Login or Register  to view this content.`

Regards, TMS

4. ## 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. ## Re: find row based on multiple criteria

Yes. Have you tried the other formula offered?

6. ## 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. ## 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

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