Generating a True or False (Advanced!)

[brian8989]

Hi Experts,

Kindly refer to the queries and photo below:

I wish to generate a "True or False" result (column I). For example, cell H3 has a number 10, and cell H4 has a number 2, i would like to have a formula to generate True of False:

If the number in cell H3 (10) is in column A , then check if the number in cell H4 (4) is in Row 12, if
NO = False, Yes = True


https://ibb.co/4SKpR8t

 

[Fluff]

Myabe
=IFERROR(AND(MATCH(H3,A:A,0),MATCH(H4,12:12,0)),FALSE)

 

[brian8989]

@Fluff Thanks for the instant reply. Is there a formula to "automatically" look for the "range"in the relevant Row?

=IFERROR(AND(MATCH(H3,A:A,0),MATCH(H4,12:12,0)),FALSE) <----instead of manually change the range (12:12), is there a way to automatically locate a

particular Row based on the result of first part (If the number in cell H3 (10) is in column A).

 

[JonXL]

Hi Experts,

Kindly refer to the queries and photo below:

I wish to generate a "True or False" result (column I). For example, cell H3 has a number 10, and cell H4 has a number 2, i would like to have a formula to generate True of False:

If the number in cell H3 (10) is in column A , then check if the number in cell H4 (4) is in Row 12, if
NO = False, Yes = True

For me, your explanation doesn't match your picture.

The explanation tells me that if I am looking for '4', I should get FALSE (based on the data in the picture), but the result in the picture shows TRUE. Yet there is no '4' in row 12 - the '4' is all the way up in row 4. Likewise for '0', which is in row 6 and not 12 though your picture shows TRUE.

Based just on the picture, I'd actually understand your problem thusly:

Look at each cell in col H (call this the check cell). Find the value of the cell above this check cell (call that the the row cell). If the value of the column H check cell appears in the row where col A's cell matches the row cell, then return TRUE; otherwise return FALSE. If there is no value in the cell above the check cell in col H, then return nothing.

If that is what you are looking for, the following should work (place in I3 and drag down):

=IFERROR(COUNTIF(INDIRECT("B"&MATCH(H2,A:A,0)&":F"&MATCH(H2,A:A,0)),H3)>0,"")

Indirect formulae aren't the best - if someone offers a solution that doesn't require it, I'd take that.

 

[Fluff]

Maybe
=ISNUMBER(MATCH(H4,INDEX(A2:G13,MATCH(H3,A2:A13,0),0),0))

 

[JonXL]

Maybe
=ISNUMBER(MATCH(H4,INDEX(A2:G13,MATCH(H3,A2:A13,0),0),0))

Use this formula - much better than mine!

 

[brian8989]

Maybe
=ISNUMBER(MATCH(H4,INDEX(A2:G13,MATCH(H3,A2:A13,0),0),0))

@Fluff Thanks friend, this almost got it work. But the result generated for other Data is incorrect if I replicate the formula.

I attached herewith a drawing illustration for your reference and better understanding of the formula I requested.



Thank you so so so much for the great help!

 

[brian8989]

@JonXL Thank you for your great help and kindly accept my apology for confusing question/explanation. I attached herewith a drawing illustration for your reference and better understanding of the formula I requested.


<a href="https://ibb.co/3hnqqxD"><img src="https://i.ibb.co/2KTQQzD/xxx.jpg" alt="xxx" border="0"></a>

 

[Fluff]

That image does really help.
Can you please explain what results you should get & why?

 

[brian8989]

@Fluff I wish to get "True" or "False"result (I don't mind if it is replaced with Yes or No OR 1 (True) or 0 (False)).

This helps me to identify if the Data in column H4 is affected by column H3.

Thank you so much for the great help!