Place Text String to Specific Row Number Identified by Number + Match ID to Same ID Embedded in String

[djmyers]

I need a formula that will place a text string in a row specified by the number from an adjacent column within a look-up table.

Additionally, I also need a formula that will return a text string based on the same row number described above, plus a match of the ID number with the same ID # embedded in a string.

The only formula I've worked out so far just matches the ID with the same ID embedded in a string:

=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:
Match String with ID to ID.xlsx

 

[bebo021999]

In B2:

=LOOKUP(2,1/SEARCH($A2,$F$2:$F$11),E$2:E$11)

What do you expect in D2?

 

[djmyers]

In B2:

=LOOKUP(2,1/SEARCH($A2,$F$2:$F$11),E$2:E$11)

What do you expect in D2?

Hi bebo021999: In columns B, C and D I'm expecting exactly the same result—the correct ALERT STRING from column G. In the simplified example shown above, such redundancy may seem superfluous. In the real-world data, it makes more sense.

So in the example you provided, I would want “Who Are The Brain Police?” to show up in cell B7 as the formula is copied down, just as it does in cell C7. My goal is for the formula to reference the number 7 shown in cell E2 and then return the string shown in G2 to the 7th row in column B as the formula is being copied down that column.

In column D, I would want the formula to reference both the row number shown in column E and the embedded ID shown in column F. In other words, the ID shown in cell A2 (for instance) is embedded in the string shown in cell F7. In this example, the correct ID row number for that ID (2) is displayed in cell E7. Thus, I would want the formula to return the alert string “Cruising For Burgers” to cell D2.

Does that make sense?

 

[bebo021999]

Is it are you looking for in E2:

=MATCH(G2,C:C,0)

??

 

[bebo021999]

In case you with a formula in E2, independent from column B:D:

=LOOKUP(2,1/ISNUMBER(SEARCH($A$2:$A$21,$F2)),ROW($A$2:$A$21))

 

[djmyers]

Is it are you looking for in E2:

=MATCH(G2,C:C,0)

??

No. In the example shown here, columns B, C and D are result columns. Each uses a different approach to get the same answer. In this example, columns A, E, F and G are the reference columns.

 

[djmyers]

These two formulas both work for the column B (Alert String to ID Identified by ID ROW) portion of my question:

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

(Major hat tip to Nayan.)

Now if I could just figure out the formula for column D…

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Align Text To Row Identified by Number and to ID Matching that Embedded in String
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[djmyers]

This is the formula I was looking for in column D:

=INDEX($G$2:$G$11,MATCH(ROW(D2)&"*"&A2&"*",INDEX($E$2:$E$11&$F$2:$F$11,),0))