Thread: Look up Formula Thanks: 0 Likes: 0

1. 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. Re: Look up Formula

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

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

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

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

9. Re: Look up Formula

Originally Posted by Fluff
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. 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.