Look up Formula

[mannychacon71]

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[TABLE="width: 500"]
<tbody>[TR]
[TD]100
[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]110[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

thank you

 

[Fluff]

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

 

[mannychacon71]

I think that answers part of my issue.
I have a template on a different sheet
[TABLE="width: 556"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Rank[/TD]
[TD]240[/TD]
[/TR]
[TR]
[TD]Level[/TD]
[TD]94

[/TD]
[/TR]
</tbody>[/TABLE]

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]

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]

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]

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

 

[mannychacon71]

[TABLE="width: 197"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]6[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]178[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]173[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]172[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]171[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]170[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]163[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]162[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]161[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]153[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]152[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]151[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]143[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]142[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]141[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]133[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]132[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]131[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]130[/TD]
[/TR]
</tbody>[/TABLE]

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]

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

 

[mannychacon71]

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]

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.