Excel Formula to check if two strings exist in the same row on a sheet without specifying the column

[Knockoutpie]

Hello everyone, i'm trying to find out if there is a way to determine if two strings are in the same row somewhere on a sheet.

Right now i'm half way there, my not so elegant solution is just to count if a string exists in a sheet.

=COUNTIF('Sheet2'!3:100,Sheet1!B5)

This formula checks Sheet 2 row 3:100 if the value of B5 in Sheet 1 exists.

=IFERROR(ROW(INDEX(Sheet2!E:E,MATCH(1,("String1"=Sheet2!E:E)*("String2"=Sheet2!H:H)*("String3)"=Sheet2!A:A),0))),"9999")

This formula will return the row number the criteria are all found on, if not found, return 9999. The only issue with this is I have to specify the column on where the strings will be. Occasionally the columns move around.

My ideal formula: Find two criteria which are in the same row, without specifying the column, and then return the row number.

Any help would be greatly appreciated.

 

[Coyotex3]

Can you post an example of the data you are working with.

This might be an option.

Assuming you want to search for the string in Column A, on range B;D

=FILTER(ROW(A1:A6),COUNTIF(B1:D6,A1:A6))

This formula returns:

 

[Knockoutpie]

Hi all, this is the answer.

=TEXTJOIN(", ",,FILTER(ROW(3:100),BYROW('Sheet2'!3:100,LAMBDA(Z,ISNUMBER(MATCH("String1",Z,0))*ISNUMBER(MATCH("String2",Z,0)))))