+ Reply to Thread
Results 1 to 2 of 2

Returning the correct value from a list of text. Functions within functions

  1. #1
    Registered User
    Join Date
    01-17-2013
    Location
    Minneapolis, MN
    MS-Off Ver
    Excel 2010
    Posts
    1

    Returning the correct value from a list of text. Functions within functions

    Hello,

    I have a list or table of titles, V.P., Vice President, President, CEO and each title has a level associated with it. Example V.P. = 3, Vice President = 3, President =2, CEO =1. I'm using the below code to search through a another list of titles I receive to associate the appropriate levels to their title. The problem I'm having with the below code is that Vice President is returning as a level 2 instead of a level 3.

    =VLOOKUP(LOOKUP(9.99E+307,SEARCH('Level List'!$A$2:$A$36,A1),'Level List'!$A$2:$A$36),'Level List'!$A$2:$B$36,{2},FALSE)

    "Level List" is the excel worksheet that contains the table of titles and their corresponding level.
    Example:
    Title Level
    Vice Pres 3
    Vice President 3
    Head of 3
    President 2
    CFO 1
    CIO 1
    COO 1
    CTO 1
    Ect.

    A1 is the title that needs a level associated with it.
    So Executive Vice President of World Domination needs to return as level 3.
    With the code I have now it is returning as level 2.

    Could someone let me know how to fix this and let me know if there is an easier way to write the function to do the process I'm trying to achieve?

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Returning the correct value from a list of text. Functions within functions

    Not sure how that SEARCH is supposed to work. You are passing it SEARCH(array, value), which I don't think works. I assume you are trying to add some smarts, so that "Vice" is found as "Vice President" even if "Vice" is not specifically called out in your 'Level List'.

    Simply, without the smarts, it should just be:
    =vlookup(A1,'Level List'!$A:$2:$B$36,2,FALSE)

    See if that provides the right answers. Then if you still want the smarts, use the 'Evaluate Formula' tool (in the Formulas ribbon) to trace you code and see where it is encountering problems.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

+ 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