# 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 110 6 115 9

<tbody>
</tbody>

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

thank you

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Maybe
=IFERROR(A2/A2,"")

#### mannychacon71

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

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

<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.

#### mannychacon71

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

#### 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.