Satisfy multiple criteria

[alanlambden]

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

 

[kweaver]

How's this?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Hole	North	East	RL						RC hole	UTM N	UTM E	Desired Result	Differ by 5
2	New_1	9091250	725308							P_1	9091250	725308	New_1
3	New_2	9091250	725358							P_2	9091250	725358	New_2
4	New_3	9091250	725408							P_3	9091250	725408	New_3
5	New_4	9091250	725558							P_4	9091250	725558	New_4
6	New_5	9091250	725608							P_5	9091250	725607		New_5
7	New_6	9091300	725285							P_7	9091300	725353
8	New_7	9091300	725353							P_8	9091300	725383
9	New_8	9091300	725450							P_9	9091300	725433
10	New_9	9091350	725508							P_10	9091350	725258	New_9
11	New_10	9091350	725558							P_11	9091350	725508
Sheet18

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

 

[alanlambden]

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

 

[kweaver]

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

 

[alanlambden]

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.

 

[kweaver]

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?

 

[alanlambden]

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

 

[kweaver]

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

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Hole	North	East	RL						RC hole	UTM N	UTM E	Desired Result
2	New_1	9091250	725308							P_1	9091250	725308	New_1
3	New_2	9091250	725358							P_2	9091250	725358	New_2
4	New_3	9091250	725408							P_3	9091250	725408	New_3
5	New_4	9091250	725558							P_4	9091250	725558	New_4
6	New_5	9091250	725608							P_5	9091250	725607
7	New_6	9091300	725285							P_7	9091300	725353	New_7
8	New_7	9091300	725353							P_8	9091300	725383
9	New_8	9091300	725450							P_9	9091300	725433
10	New_9	9091350	725508							P_10	9091350	725258
11	New_10	9091350	725558							P_11	9091350	725508	New_9
Sheet18

Cell Formulas
Range	Formula
M2	{=IFERROR(INDEX($A$2:$A$11,MATCH(K2&L2,$B$2:$B$11&$C$2:$C$11,0),1),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[alanlambden]

That worked kweaver!

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

 

[kweaver]

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