Look up Formula

mannychacon71

New Member
Joined
Sep 11, 2019
Messages
5
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hi & welcome to MrExcel.
Maybe
=IFERROR(A2/A2,"")
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
If 100 is level 1 & 420 is level 75, how do you get 240 is level 94?
That makes no sense to me?
 
Upvote 0
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
 
Upvote 0
Do you have that list anywhere in the workbook? If so you can use an index/match formula to return the level.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top