help with a forumla

[supermax77]

Hi
i have three COLUMNS of data and i want to search each ROW to get a yes/no as to whether or not a list of five items are in ANY of the 3 columns for each ROW.

thanks

Max

 

[hagia_sofia]

Hello, could you please upload a sample of your data?

 

[supermax77]

 

[hagia_sofia]

Sorry, one more question - how should the output look like?

 

[supermax77]

Sorry, one more question - how should the output look like?

a flag (1) for yes and 0 for no match

 

[Scott Huish]

=--(COUNT(MATCH($I$2:$I$6,B2:D2,0))>0)

Copy down.

Book1
	A	B	C	D	E	F	G	H	I
1	ID	Prim DX	AD1	AD2	MH Dx Match
2	xx	F43.0	R45.81		1				F1*
3	xx	F43.1	F19.1		1				F2*
4	XX	F25.0			1				F3*
5					0				F4*
6	xx	J96.0			0				F5*
7
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=--(COUNT(MATCH($I$2:$I$6,B2:D2,0))>0)

 

[supermax77]

thank you. but when i enter this i only get Zeros and, blanks and > but these are not accurate

i wonder if my look up values aren't formatted correctly, I have said F1* because i want the formula to include any value with an F1 in it - as a match..

is this what is causing the issue?
I cant work out how to upload my spreadsheet

 

[hagia_sofia]

Hello, could you please test this:

=BYROW(B2:D28,LAMBDA(a,IF(SUM(--(ISTEXT(XLOOKUP(I2:I6,a,a,,2))))>0,1,0)))

 

[Scott Huish]

thank you. but when i enter this i only get Zeros and, blanks and > but these are not accurate

i wonder if my look up values aren't formatted correctly, I have said F1* because i want the formula to include any value with an F1 in it - as a match..

is this what is causing the issue?
I cant work out how to upload my spreadsheet

I interpreted that as starting with F1, F2 etc. Can it appear in the middle of the string? If you look at my sample, if you mean it starts at the beginning of the string that my formula does that.
1 if any of those values appear in the row, 0 if it doesn't

 

[felixstraube]

If the F1, F2,... can appear anywhere in the text:

Book1
	A	B	C	D	E	F	G	H	I
1	ID	Prim DX	AD1	AD2	MH Dx Match
2	xx	F43.0	R45.81	F1	1				F1
3	xx	F43.1	F19.1		1				F2
4	XX	F25.0		F1	1				F3
5					0				F4
6	xx	J96.0	F81		0				F5
Sheet9

Cell Formulas
Range	Formula
E2:E6	E2	=(COUNT(MATCH("*"&$I$2:$I$6&"*", B2:D2,0))>0)*1