locked
Search Text in Column RRS feed

  • Question

  • HI Guys,

    I was trying to enter a column value based on an expression.

    I want to search a column which has a certain text(using wild card) and vlookup with a different column if the value matches insert in the column value from second column.

    For example,

    I have to find Linux and enter it in a column .

    Sheet 1 Col1 has Linux,IBM,Oracle

    Sheet 2 COl2 has Linux

    SHeet 3 Col4 must be LInux based on Vlookup.

    IF (Sheet 1 Col1)matches (Sheet 2 COl2) then SHeet 3 Col4=Sheet 2 COl2

    Monday, December 31, 2012 5:09 AM

Answers

  • This sounds like a purely Excel question.  If I'm reading this correctly, I'd suggest posting in the MSDN Excel forum or on MrExcel.

    For a native Excel solution, assuming you are comparing corresponding rows in the defined columns, you can use the SEARCH and FIND functions in Excel to search a string for the existence of another string and return the starting position of a match.  Then, if that starting position is a valid value, return your lookup value in the target.  Something like:

    =IF(ISERROR(SEARCH(C2,A2)),"",C2)

    Hope that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Monday, December 31, 2012 3:39 PM
    Answerer
  • Yes.  Give it a try.  More examples and details here.

    http://office.microsoft.com/en-us/excel-help/check-if-a-cell-contains-text-HP003056106.aspx


    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

    • Proposed as answer by Elvis Long Wednesday, January 2, 2013 3:21 AM
    • Marked as answer by Elvis Long Tuesday, January 8, 2013 1:34 AM
    Monday, December 31, 2012 7:22 PM
    Answerer

All replies

  • This sounds like a purely Excel question.  If I'm reading this correctly, I'd suggest posting in the MSDN Excel forum or on MrExcel.

    For a native Excel solution, assuming you are comparing corresponding rows in the defined columns, you can use the SEARCH and FIND functions in Excel to search a string for the existence of another string and return the starting position of a match.  Then, if that starting position is a valid value, return your lookup value in the target.  Something like:

    =IF(ISERROR(SEARCH(C2,A2)),"",C2)

    Hope that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Monday, December 31, 2012 3:39 PM
    Answerer
  • Does this work for wildcard search,I mean A2 has multiple values seperated by comma.
    Monday, December 31, 2012 4:59 PM
  • Yes.  Give it a try.  More examples and details here.

    http://office.microsoft.com/en-us/excel-help/check-if-a-cell-contains-text-HP003056106.aspx


    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

    • Proposed as answer by Elvis Long Wednesday, January 2, 2013 3:21 AM
    • Marked as answer by Elvis Long Tuesday, January 8, 2013 1:34 AM
    Monday, December 31, 2012 7:22 PM
    Answerer