How to match a row in a table and check if it contains a given value (COUNTIF + VLOOKUP)?

Hello, how can I check if a row in a table contains a given value? I was thinking to use COUNTIF + VLOOKUP but it doesn't work, because I can't use the VLOOKUP as a range in the COUNTIF formula.

	A	B	C	D	E	F
1	ID	C1	C2	C3	C4	C5
2	A	1	6	13	19	33
3	B	2	5	16	22	29
4	C	3	7	14	29	35

I have the ID and I have to check if the ID row contains the given value, my idea was to use this formula:

Formula:

=COUNTIF(VLOOKUP("B",A2:F4,{2,3,4,5,6},FALSE),22)

Because the B row contains the value 22 I was expecting to get a 1, but excel doesn't accept the formula.
How can I solve this problem?

Thanks, excel says that the function isn't valid
Anyway I don't understand the filter part (f>=2)*(f<=6), how should this find if the row contains the value 22?

See attachement

Thanks for the reply but actually I don't have to find the row containing the 22, I have the row ID and I have to check if the row contains the given number, i.e. does the row A contains the number 8? No. Does the row C contains the value 35? Yes. etc.

Originally Posted by AliGW

Isn't that what my solution does??? Maybe you failed to spot it ...

Your attempt seemed to have the range 2 to 6. If you want 22, use this:

=LET(f,FILTER(B2:F4,A2:A4="B"),COUNT(FILTER(f,f=22)))

Sorry, it was a reply for TDus

Originally Posted by HansDouwe

Put the row in I1 and the value in I2 and try

Formula:

=ISNUMBER(MATCH(I2,INDEX(B2:F4,MATCH(I1,A2:A4),0),0))

Thanks, it worked, I had just to add an exact match for the MATCH function:

Formula:

=ISNUMBER(MATCH(I2,INDEX(B2:F4,MATCH(I1,A2:A4,0),0),0))

Originally Posted by TDus

Here you go.
(mind that H3 and H4 should be I2 and I3 instead)

Thanks, this is working too

Nice one Tdus, tried something similar with hlookup, but i settled on a lazier solution.

Attachment 808514

Row 4 is a simple vlookup with the value in B4.
J1 is a HLookup too.
Tdus's answer is more elegant.