0

I am trying to take column a (5000 rows) and match against column b (138 rows) and when it finds a match in cell b will take cell c (on the same row) and print it in cell d for example. I have attempted to use an IF statement and a VLOOKUP but just can't seem to get it correct.

The problem with using a nested IF statement is column b is 138 to 150 categories. 

       Column a            Column b        Column c            Column d

        this is orange      Fitness Bug     Fitness         Should print "Orange Fruit"
        this is yellow      Jack Rabit      Jacks           Should print "Bees"
        this is ebay        Yellow Jacket   Bees            Should print "Websites"
        this is Rabit       Ebay Website    Websites        Should print "Jacks"
        this is fitness     Oranges         Orange Fruit    Should print "Fitness"
pnuts
  • 58,317
  • 11
  • 87
  • 139
user2385949
  • 1

1 Answers1

1

=""""&VLOOKUP(INDIRECT("B"&MATCH("*"&A1&"*",B:B,0)),B:C,2,FALSE)&"""" in the top row and copied down.

Edit (Based on clarification from comment and re-interpretation of question) 

="Should print """&VLOOKUP(INDIRECT("B"&MATCH("*"&MID(A1,9,LEN(A1)-7)&"*",B:B,0)),B:C,2,FALSE)&""""
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Thanks for the response, tried the above and placed formula in Cell D1 and received an "#N/A" response? – user2385949 May 17 '13 at 05:50
  • 1
    A1 contains "this is orange". and if you click on d1 it contains exactly the formula =""""&VLOOKUP(INDIRECT("B"&MATCH("*"&A1&"*",B:B,0)),B:C,2,FALSE)&"""" Excel Version 2007 – user2385949 May 17 '13 at 21:50