-1



Many thanks to CAROL! for such a quick and perfect answer! AWESOME!

Thank you in advance for help!!!

So...I have about 30 rows of values in column A. I have about 250 rows of 'sentences' in column B. I need to find where any (string) value in column A appears in a sentence in column B, and where exists I need to display in blank column C which matching column A value exists on said B-row. There will never be an occasion where more than one column A value exists in the same column B cell.

For example:
The value in A2 ("TA12345") exists in the sentence in B18 ("take the dog to TA12345 and let him run"); I need a formula that finds that match and returns in C18 the value "TA12345"). And I need that formula to do this for all values in column A. Is this possible? I'm about to pull the last of my hairs out on this one. I'm not very advanced in Excel to begin with, so bear with me on troubleshooting responses. Thank you so much!!!

Side note......I have tried every formula I've found in various similar threads, and none of them works for me - all of them show N/A or No Match, etc., when I know for a fact colA values do exist in colB and I need to pinpoint where. There were many answers in this thread (Excel: Check if Cell value exists in Column, and then get the value of the NEXT Cell) - promise none of them works :(

squideast
  • 1
  • 1

1 Answers1

0

Put this in cell C1 & drag down. You'll need to enter your actual column A range or change it to a named range. It assumes you want blank in cell C if no match.

=IFNA(INDEX($A$1:$A$30,IF(SUMPRODUCT(ISNUMBER(SEARCH($A$1:$A$3,B1))*ROW($1:$30))=0,#N/A,SUMPRODUCT(ISNUMBER(SEARCH($A$1:$A$30,B1))*ROW($1:$30)))),"")
Carol
  • 471
  • 4
  • 7
  • Oh my goodness there are results! SO EXCITING! Something is funky with them, and I'll troubleshoot for a bit before writing back, but there are col A values showing up in col C! Funkiness is that what's showing in col C doesn't represent the 'TANNNNN' that is found in the sentence in col B. I can't figure out yet what the rhyme/reason might be - I'll write more in a bit. – squideast Nov 21 '17 at 19:44
  • aha - figured it out - it's showing the 'found' col A value in the next row's col C cell; so if the value TA12345 were in A2 and found in the sentence in B18, it's showing TA12345 in C19. i can work with that! thank you SO very much, carol. *much love*!!! – squideast Nov 21 '17 at 19:46
  • Your data must have had a slightly different layout than I wrote the formula for. Glad you sorted it. If you could upvote or mark as the answer I’d appreciate it. Thanks. – Carol Nov 21 '17 at 19:59
  • You're right - I had a title row in row 1, and data starting on row 2 - so I should have adjusted for that. I tried to upvote it immediately, but it won't let me because I'm a newbie/have no points. I'll go edit my question to give you kudos there at least! – squideast Nov 21 '17 at 20:44