+ Reply to Thread
Results 1 to 13 of 13

Search for TEXT and assign a specific number

  1. #1
    Registered User
    Join Date
    08-28-2010
    Location
    uganda
    MS-Off Ver
    Excel 2003
    Posts
    5

    Search for TEXT and assign a specific number

    Hi dudes
    i am new here so hello all!
    i dont have so much experience with Excel so i need a lilttle help if possible
    The problem is the following one:

    In A1 i have a list with alot veicles description and that list has many brands lets say just some of them for now:
    bmw, mercedes, fiat, peugeot, alfa romeo, lotus, ferrari but they are OVER 40.

    Veicles descriptions can be like this:

    * Special offer very good condition bmw 318 year 2002 color black
    * In offer a new aston martin model x5 0km year 2010
    * Good conditions fiat lancia 2.0 year 1990
    * Second hand really crap mercedes from 80's

    So i need a formula which search in the the colum A1 what kind of brand the veicle is (if contain ferrari, if contain fiat, if contain bmw) and then fetch the number from the corrisponding brand which is specified in Sheet2, if not found then i need some error to check manually and evenctually add to sheet2 the new brand and assign to him a new number never used before. The result must be placed on A2 and should be something like that:
    A1 A2
    Good conditions fiat lancia 2.0 year 1990 ------ 45 (45 because is the corrisponding number to lancia in sheet2)

    Well it is impossible to me to check cell per cell what kind of veicle is inside then go back in the brand database check the corrispettive number and write it by hand, for some reasons i need a formula doing without using filters.
    I have tried using if nested formulas but with over 40 brands doesnt works.
    How can i do plix plox? sorry for my english i hope you understand something.

    in attachment images for example, sorry for my english

    Thank you
    Attached Images Attached Images
    Last edited by Sub7; 08-28-2010 at 08:43 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 for TEXT and assign a specific number

    If you want a quick response post an Excel file not images.

    based on your examples:

    Please Login or Register  to view this content.
    In recompense I will take the Aston Martin.

  3. #3
    Registered User
    Join Date
    08-28-2010
    Location
    uganda
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Search for TEXT and assign a specific number

    Hi, thanks for answer and sorry for images i attached now my workbook.
    u can cut and paste the aston if u like it :D
    Attached Files Attached Files
    Last edited by shg; 08-28-2010 at 12:06 PM. Reason: deleted quote

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

    Re: Search for TEXT and assign a specific number

    The formula provided will work you simply need to change Sheet2 to category so as to reflect the real sheet names in your file.

  5. #5
    Registered User
    Join Date
    08-28-2010
    Location
    uganda
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Search for TEXT and assign a specific number

    Yo, dunno why but with that formula:
    =LOOKUP(9,99E+307;SEARCH(category!$A$2:$A$6;$A2);category!$B$2:$B$6)

    i get error #NAME?

    PS: i had to change the , with ; otherwise i get formula error. i use office 2003

    thanks
    Last edited by shg; 08-28-2010 at 12:09 PM. Reason: deleted quote

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Search for TEXT and assign a specific number

    What language version of Excel do you have?
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Registered User
    Join Date
    08-28-2010
    Location
    uganda
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Search for TEXT and assign a specific number

    Quote Originally Posted by shg View Post
    What language version of Excel do you have?
    Hello, I use italian s.o.
    i changed the name of functions lookup and search with italian function but no success.
    dunno, anyway i am able to open english files.

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

    Re: Search for TEXT and assign a specific number

    see the attached (function names will auto translate when opened)
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-28-2010
    Location
    uganda
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Search for TEXT and assign a specific number

    Work perfectly many thanks

  10. #10
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    Exclamation Doubt !

    Dear DonkeyOte, Good Afternoon.

    Please, which is the meaning of 9,99E+307 in the function?

    Maybe istīs a basic question for all of you, but never is to late to learn a little more.

    Thanks in advanced.

    Have a nice weekend.
    ...If my answer helped you, Please, click on. * Add Reputation (at left)

    Best regards.
    Marc?lio Lob?o

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

    Re: Search for TEXT and assign a specific number

    9,99E+307 (or 9.99E+307 pending location) is a very big number: 9.99*10^307

    The significance of this big number stems from the nature of LOOKUP logic:

    Quote Originally Posted by http://www.excelforum.com/2252293-post5.html
    LOOKUP assumes all values in the lookup_vector are sorted in ascending order (irrespective of reality)

    LOOKUP returns last value <= criteria in the lookup_vector (or associated value from result_vector if specified)

    LOOKUP ignores all values in the lookup_vector that are not of the same data type as the criteria - this (importantly) includes error values
    ie error values in the lookup_vector won't cause the LOOKUP to fail.
    In this instance the lookup_vector is populated with an array of SEARCH results (one per search term).
    These results will either be numbers or error values - former where term is found, error value otherwise

    It follows that given the criteria value will exceed all values within the lookup_vector the LOOKUP will use the last numeric result given it is assumed to be the biggest and errors are simply ignored.
    The addition of the result_vector means that instead of returning the last number from the lookup_vector the function instead returns the associated value from the result_vector.

    From the above you can deduce that this approach will return the last search term listed that is found in the criteria string should more than one term exist.
    I have made the very deliberate assumption that this approach is valid.
    (eg. "volvo bmw" would return the value associated with whichever of the car makers was listed last on category sheet)

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Search for TEXT and assign a specific number

    Further to DO's point, 9.99999999999999E+307 is the largest number that Excel allows you to enter from the user interface. 9.99E307 is a little more compact appearing in formulas while still being astronomically large.

    I have a named constant in my standard template (Book.xls): conBig refers to =9.99999999999999E+307, and so would use =LOOKUP(conBig, ...

    Another handy one is conZzz refers to =žžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžžž
    Last edited by shg; 08-28-2010 at 03:07 PM.

  13. #13
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    Thumbs up Thank You Very Much!

    Dear DonkeyOte and shg,

    Thank You Very Much !

    Yours explanation was very clear and clever too.

    .

+ 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