Search code examples
formulaspreadsheet

Align Text To Row Identified by Number and to ID Matching that Embedded in String

I need to align text in an ALERT STRING column with the row identified by number in an ID ROW column.

Additionally, I need to also align the same ALERT STRING text with the same ID ROW number AND with the ID matching that embedded in a string in the TEXT WITH ID column. (This double-check will sometimes be necessary with the real-world data.)

So far, I've only figured out how to align the ALERT STRING with the ID matching that embedded in the TEXT WITH ID column: =LOOKUP(2,1/SEARCH(A2,$F$2:$F$11),$G$2:$G$11)

I appreciate any help folks can offer. You can find an editable copy of the workbook here: https://1drv.ms/x/s!ArQ7Kw6ayNMY2zktTW3pDCbMmJZ_

UPDATE: Nayan provided a solution to the first part of this question (please see answer below). I'm still trying to work out a formula for the column D part of this question, in which the row reference shown in column E is combined with a match of the ID shown in column A with its corresponding value in one of the text strings in column F.

The best I've been able to come up with so far is a formula with a high failure rate: =INDEX($G$2:$G$11,MATCH(ROW(D2),$E$2:$E$11,MATCH("*"&A2&"*",$F$2:$F$11,0)))

Any help with this part of the question will be greatly appreciated.

Alert String to ID

Solution

  • ROW([reference])

    • Returns the row number of a reference
    • E.g.: Row(B2) returns 2. If nothing provided like ROW() will also return row number based on position of cell where it is called.

    VLOOKUP(loolup_value, table_array, col_index_num, [range_lookup])

    • Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify (col_index_num)
    • By default - the table must be sorted in an ascending order.

    Try this:

    =VLOOKUP(ROW(B2),$E$2:$G$11,3,FALSE)

    INDEX(array, row_num, [column_num]) INDEX(reference, row_num, [column_num], [area_num])

    • Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

    In this case, you have to get row_num with MATCH function.

    MATCH(lookup_value, lookup_array, [match_type])

    • Returns a relative position of an item in an array that matches a specified value in a specified order.
    • match_type: 1 (Less than), 0 (Exact match), -1 (Greater than)

    Try this:

    =INDEX($G$2:$G$11,MATCH(ROW(B2),$E$2:$E$11,0))

    Identity Data with Multiple Criteria Condition using MATCH()

    =INDEX($G$2:$G$11,MATCH(1, (ROW(D2) = $E$2:$E$11) * (ISNUMBER(SEARCH(A2, $F$2:$F$11))),0))

    References:

    https://exceljet.net/excel-functions/excel-vlookup-function

    https://exceljet.net/excel-functions/excel-index-function

    https://exceljet.net/formula/index-and-match-with-multiple-criteria