+ Reply to Thread
Results 1 to 7 of 7

Search word within Cell (Vlookup+Search)

  1. #1
    Registered User
    Join Date
    12-01-2008
    Location
    India
    MS-Off Ver
    Microsoft 365 Enterprise - Excel version2301
    Posts
    63

    Search word within Cell (Vlookup+Search)

    Hi All,

    I have a set of data which contains " Account Heads" in Column "A" and "Data" in Column "B", "Data" contains "Account Heads" with some other text as well, i want to have the Account Head in Column "C" based on the "Account Heads" from "Data"


    Please see the attached sheet for refernce.


    regards and Thanks in Advance.
    Attached Files Attached Files
    Last edited by Amarjeet Singh; 02-03-2009 at 10:18 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Search word within Cell (Vlookup+Search)

    If you assume only one instance of A value in B string then perhaps the following would work for you ?

    C2: =LOOKUP(2,1/ISNUMBER(SEARCH($A$2:$A$19,$B2)),$A$2:$A$19)
    copy down for values in B.. you will get an error if no terms can be found.

    Can you confirm which version of Excel you are running ?

  3. #3
    Registered User
    Join Date
    12-30-2008
    Location
    Vermont, USA
    MS-Off Ver
    Excel 2003
    Posts
    64

    Re: Search word within Cell (Vlookup+Search)

    That is a very interesting solution, DonkeyOte-- I am completely baffled how it works-- don't understand how SEARCH works when entered as you have it and don't understand how you can get anything besides one of these two situations:
    lookup 2 in the array {1} and return the result from A$2:A$19
    or
    lookup 2 in the array {#DIV/0} and return the result from A$2:A$19

    but it works perfectly, which is why I'm so baffled! [sorry, not trying to start another question in this thread!]

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Search word within Cell (Vlookup+Search)

    Clownfish, for the best explanation highlight a formula that returns a result and then use Evaluate Formula to step through so you can see how XL generates it's result...

    In short the LOOKUP will generate an array (lookup_vector) of 18 values each of which being the result of the the SEARCH of A2 to A19 within the string in B... the ISNUMBER will obviously return a combination of FALSE's (not found) or TRUE's (found) ... the 1/Boolean result will thus coerce the Booleans to Integer (0/1 respectively) & in turn revise the 18 values to either 1 (TRUE... 1/1) or #DIV/0! (FALSE... 1/0) ...

    With the lookup_value set to 2 it will ignore any values not of the same type thus #DIV/0!s are ignored ... and given 2 exceeds the largest possible value in the lookup_vector the LOOKUP will return the last numeric value ... and thus wherever that last 1 is found in the 18 value array the corresponding value from the result vector (itself set to A2:A19) will be returned.

    Hope that makes some sense... as I say check out the Evaluate Formula option... it's very handy in these situations.

    (we're assuming there's only instance of A2:A19 within the B string.. ie not A2 AND A10 for ex... so no concatenation of strings required).
    Last edited by DonkeyOte; 01-30-2009 at 10:04 AM. Reason: trying to make it make sense !

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Search word within Cell (Vlookup+Search)

    was looking at this and found a few probs
    OpExp-S/LTax-ReclassIncomeTax

    OpExp-S/LTax-SalesPenalties

    are two of them that contain two poss dept heads

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Search word within Cell (Vlookup+Search)

    Yes, as I said the formula was based on premise of one match.

    For 1+ I think UDF would be the way to go - so perhaps along the lines of:

    Please Login or Register  to view this content.
    Called from C2:

    =DEPTHEADS($A$2:$A$19,$B2,",")
    copied down

  7. #7
    Registered User
    Join Date
    12-30-2008
    Location
    Vermont, USA
    MS-Off Ver
    Excel 2003
    Posts
    64

    Re: Search word within Cell (Vlookup+Search)

    DonkeyOte-
    thanks very much for the explanation; always learning something new here!

    Never used the "Evaluate Formula" option-- will take a look....

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1