Results 1 to 10 of 10

Thread: Look up Formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Look up Formula

    I am trying to create a formula:

    I want the formula to do as follow

    if cell A2 = any number for example 100
    then cell B2 will equal 1
    100 1
    110 6
    115 9

    the purpose of this is to match the database with license numbers and ranking based on those license numbers

    thank you

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Look up Formula

    Hi & welcome to MrExcel.
    Maybe
    =IFERROR(A2/A2,"")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Look up Formula

    I think that answers part of my issue.
    I have a template on a different sheet
    Rank 240
    Level 94

    the example above shows that if i pick Rank 240 from a drop down list
    then the lever should be 94 which is picked from another drop down list

    how can i do that?

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Look up Formula

    I've absolutely no idea, what is the relationship between between 240 & 94?
    Also if the level is dependant on the rank why have it in a dropdown?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Look up Formula

    the rank determines the level, for example rank 420 equals level 75, rank 100 equals level 1. the reason i used two different drop down is because they are on a different tab and under two different columns on that tab

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Look up Formula

    If 100 is level 1 & 420 is level 75, how do you get 240 is level 94?
    That makes no sense to me?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Look up Formula

    6 180
    1 178
    5 173
    5 172
    5 171
    5 170
    4 163
    4 162
    4 161
    4 160
    12 153
    11 152
    10 151
    9 150
    8 143
    7 142
    6 141
    5 140
    4 133
    3 132
    2 131
    1 130

    based on the information above, i want that information transferred to a template on another tab, so if on cell B2 of the template i picked 131 then right below on cell C2 the hierachy would pop up 2. If i pick picked rank 150 then it would pop up the corresponding number for the hierachy


    thank you

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Look up Formula

    Do you have that list anywhere in the workbook? If so you can use an index/match formula to return the level.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Look up Formula

    Quote Originally Posted by Fluff View Post
    Do you have that list anywhere in the workbook? If so you can use an index/match formula to return the level.
    Yes I do

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Look up Formula

    In that case you can replace the "level" dropdown with a formula such as
    =index(Sheet1$B$2:$B$100,match(B2, Sheet1!$A$2:$A$100,0))
    Change the sheet name & ranges to suit.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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