Look up Formula

mannychacon71

New Member
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
1106
1159

<tbody>
</tbody>

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

thank you
 

mannychacon71

New Member
I think that answers part of my issue.
I have a template on a different sheet
Rank240
Level94


<colgroup><col><col></colgroup><tbody>
</tbody>

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?
 

Fluff

MrExcel MVP, Moderator
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?
 

mannychacon71

New Member
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
 

Fluff

MrExcel MVP, Moderator
If 100 is level 1 & 420 is level 75, how do you get 240 is level 94?
That makes no sense to me?
 

mannychacon71

New Member
6180
1178
5173
5172
5171
5170
4163
4162
4161
4160
12153
11152
10151
9150
8143
7142
6141
5140
4133
3132
2131
1130

<colgroup><col><col></colgroup><tbody>
</tbody>

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
 

Fluff

MrExcel MVP, Moderator
Do you have that list anywhere in the workbook? If so you can use an index/match formula to return the level.
 

Fluff

MrExcel MVP, Moderator
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.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top