Excel Equation Help!

[ruawzrd]

[FONT=&quot]How do I write an equation in excel that has the following format:[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]IF "C3 is found anywhere in Column H"
then IF "D4 is equal to either Column I or J (on the same row as where C3 was found in Column H)"[/FONT]
[FONT=&quot]then "Pass", Else "Fail"[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]this will be continued for the rows, e.g. search for C4 anywhere in column H, and match D5 to either column I/J on same row that C4 was found in column H).. and so forth [/FONT]

 

[Michael M]

Is this what you need

=IF(COUNTIF(H:H,C3)>0,"Pass",IF(COUNTIF(I:J,D4)>0,"Pass","Fail"))

 

[Michael M]

Bit shorter

=IF(OR(COUNTIF(H:H,C3)>0,COUNTIF(I:J,D4)>0),"Pass","Fail")

 

[ruawzrd]

Thank you! However this code seems to search the entire I and J column.

I need it to first find C3 anywhere in COLUMN H.
If it finds it for example at H10, then it needs to check if D4 equals to either I10 or J10 (not the entire column)

You also have an OR statement at the beginning, but should it be AND instead? Since IF C3 is found in Column H, THEN (and) D4 is found on that same row in column I or J, then pass

 

[Michael M]

Ok, I see your issue.....will have to give that more thought...someone else may jump in in the meantime!

 

[Marcelo Branco]

Maybe...

=IF(ISNUMBER(MATCH(D4,INDEX(I:J,MATCH(C3,H:H,0),0),0)),"Pass","Fail")

M.

 

[ruawzrd]

Maybe...

=IF(ISNUMBER(MATCH(D4,INDEX(I:J,MATCH(C3,H:H,0),0),0)),"Pass","Fail")

M.

This seems to work. Thank you! Will let you know if any issues