if-statementgoogle-sheetsmatchvlookuptextjoin

Find match in a specified column, then find match in the matched row?

I have a dataset that looks something like this.

enter image description here

I'd like to make a sheet that pulls that information in this way:

enter image description here

So I want to write a formula that returns the date in Sheet2, row 4, of the column that matches the string "Lock" in the row whose A-column cell matches a given number.

I've tried writing the formula for Sheet1 myself, but this is as far as I can figure out:

=INDEX(Sheet2!$4:$4, 0, MATCH("Lock", <range>, 0))

That <range> is stumping me.

A "one-dimensional array" is called for. So I think I need a formula piece that searches the A-column for e.g., "103", and return that matching row in A1-notation. How do I do this?

The actual data set is much larger, spanning columns up to EF, down about 150 rows.

Solution

  • one-dimensional array means either:

    enter image description here

    or:

    enter image description here

    so if you want to use MATCH, searched value "Lock" needs to be anywhere in those green cells either in horizontal or vertical array. then the result will be the position of match

    see if this helps:

    =INDEX($4:$4, 0, MATCH("Lock", FILTER(A5:Z, A5:A=103), 0))

    enter image description here

    UPDATE 1:

    =INDEX(SPLIT(TOCOL(IF(B3:L="Lock", A3:A&"×"&B2:L2, ), 1), "×"))

    enter image description here

    in your sheet:

    =INDEX(SPLIT(TOCOL(IF(Sheet2!B5:Z=B1, Sheet2!A5:A&"×"&Sheet2!B4:Z4, ), 1), "×"))

    enter image description here

    UPDATE 2:

    =INDEX(IFNA(VLOOKUP(A2:A, SPLIT(TOCOL(IF(REGEXMATCH(Sheet2!B6:Z, 
 "(?i)\b"&B1&"\b"), Sheet2!A6:A&"×"&Sheet2!B4:Z4, ), 1), "×"), 2, 0)))