Lookup or If function

[jessihg12]

I am currently trying to create a formula that will do the following:

Numbers in Column F contain numbers assigned to companies

If # is between:
5 - 25 = Tier 4
26-100 = Tier 3
101 - 500 = Tier 2
500+ = Tier 1

I would like the formula to look in the F column and auto populate the tier that company falls under in column G. We have nearly 3,000 in the system so doing this manually is not ideal.

If needed, I can create a hidden sheet that this formula can look.

Example of desired end result:
Company Name Company Number Tier
Company 1 15 4
Company 2 106 2
Company 3 782 1
Company 4 48 3


I appreciate your advice. Thank you!

 

[Jonmo1]

Re: Lookup or If function help

Welcome to the board.

Try a lookup

="Tier "&LOOKUP(F2,{5,26,101,501},{4,3,2,1})

 

[jessihg12]

Re: Lookup or If function help

Example of desired end result:
Company Name Company Number Tier
Company 1 15 4
Company 2 106 2
Company 3 782 1
Company 4 48 3

I have tab spaces in between the columns, but it is running together now that the thread is published (3 columns). Company name, Number, Tier. I'm sorry if that looks confusing.

 

[jessihg12]

Re: Lookup or If function help

This worked perfectly. Thank you so much!

 

[Jonmo1]

Re: Lookup or If function help

You're welcome.