# Thread: Satisfy multiple criteria Thanks:  5 Post #5339563 (1)Post #5339793 (1)Post #5339568 (1)Post #5339580 (1)Post #5339614 (1) Likes:  5 Post #5339580 (1)Post #5339614 (1)Post #5339563 (1)Post #5339793 (1)Post #5339568 (1)

1. ## Satisfy multiple criteria

Hi,

I have two sets of data, each set has several columns of data. I want to be able to flag any rows that have two numbers that are identical? Here is an example:

https://imge.to/i/vgKLRV

I hope that link work let me know if it doesn't.

I want to flag the right column (M) only if the numbers in column B matches exactly with the number in column K AND the number in column C matches column L. If either dont match, then leave blank.

PS, is there an easy way to see if the numbers differ by less than 5? This could go in the column N

2. ## Re: Satisfy multiple criteria

How's this?

Excel 2010
ABCDEFGHIJKLMN
1HoleNorthEastRLRC holeUTM NUTM EDesired ResultDiffer by 5
2New_19091250725308P_19091250725308New_1
3New_29091250725358P_29091250725358New_2
4New_39091250725408P_39091250725408New_3
5New_49091250725558P_49091250725558New_4
6New_59091250725608P_59091250725607New_5
7New_69091300725285P_79091300725353
8New_79091300725353P_89091300725383
9New_89091300725450P_99091300725433
10New_99091350725508P_109091350725258New_9
11New_109091350725558P_119091350725508

Sheet18

Worksheet Formulas
CellFormula
M2=IF(AND(B2=K2,C2=L2),A2,"")
N2=IF(M2<>"","",IF(ABS(C2-L2)<5,A2,""))

3. ## Re: Satisfy multiple criteria

This works on a row by row basis, sometimes the numbers will be a match, but on different rows.

4. ## Re: Satisfy multiple criteria

You'll have to give me an example or two.

5. ## Re: Satisfy multiple criteria

Do you see in my image how new_9 is matching identically (B and C = K and L) with P_11, they are on different rows. You code works only if the values match on the same row.

Im sure we have to use an array or a vlookup but i can quite get it working.

6. ## Re: Satisfy multiple criteria

It's not making sense to me, unfortunately. B11 matches K10 and K11. The first match is K10. So, since C11 exists somewhere in L (It's in L5), the answer is the first match of B11?

7. ## Re: Satisfy multiple criteria

https://imge.to/i/vgWufG

Hopefully this image makes it a bit clearer. Criteria in both columns must match, but they could be located on different rows

8. ## Re: Satisfy multiple criteria

I think that made the difference for me. See if this is correct:

Excel 2010
ABCDEFGHIJKLM
1HoleNorthEastRLRC holeUTM NUTM EDesired Result
2New_19091250725308P_19091250725308New_1
3New_29091250725358P_29091250725358New_2
4New_39091250725408P_39091250725408New_3
5New_49091250725558P_49091250725558New_4
6New_59091250725608P_59091250725607
7New_69091300725285P_79091300725353New_7
8New_79091300725353P_89091300725383
9New_89091300725450P_99091300725433
10New_99091350725508P_109091350725258
11New_109091350725558P_119091350725508New_9

Sheet18

Array Formulas
CellFormula
M2{=IFERROR(INDEX(\$A\$2:\$A\$11,MATCH(K2&L2,\$B\$2:\$B\$11&\$C\$2:\$C\$11,0),1),"")}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

9. ## Re: Satisfy multiple criteria

That worked kweaver!

Is there a way to adjust it so the numbers aren't an exact match but the numbers differ by 5?

10. ## Re: Satisfy multiple criteria

I'll look into that when I have some time today.

## User Tag List

#### Posting Permissions

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