CODEWITHSUNDEEP
excelexcel-formulavba
Dino Blažević
Dino Blažević

Reputation: 27

Search for word and add additional word to it and together copy to new row

I need small help. My problem is:

In sheet A under column A and B I have following:

       Column A       |     Column B
----------------------|-------------------
 New Orleans, Buffalo |  Miami, Atlanta 
        etc           |       etc
         .            |        .
         .            |        .

I need formula to compare this words with Column C full names

    Column C
New Orleans Saints
Miami Doplhins 
       etc
        .
        .

And Paste them to lets say Sheet 2 under column A and B as full name

       Column A     |    Column B
--------------------|-------------------
 New Orleans Saints |  Miami Dolphins
         etc        |       etc
          .         |        .
          .         |        .

Upvotes: 0

Views: 48

Answers (1)

Mrig
Mrig

Reputation: 11702

Assuming your data is consistent i.e. Column A and Column B are in format xxx, yyy and you want to search for xxx in Column C, then as per image below enter the following formula in Cell E2

=IFERROR(INDEX($C$2:$C$50,MATCH(LEFT(A2,(FIND(",",A2,1)-1))&"*",$C$2:$C$50,0)),"")

Drag/Copy this formula to Column F and down as required. Change range as per your data.

enter image description here

Or if you want result in Sheet2 then enter following formula in Cell A2 of Sheet2

=IFERROR(INDEX(Sheet1!$C$2:$C$10,MATCH(LEFT(Sheet1!A2,(FIND(",",Sheet1!A2,1)-1))&"*",Sheet1!$C$2:$C$50,0)),"")

Drag/Copy this formula in Cell B2 and down as required. Change range as per your data.

Upvotes: 1

Related Questions