How to lookup for a value based on the adjacent number?

morning

New Member
Joined
Nov 23, 2016
Messages
2
Hello guys,

I'm stuck with a simple problem and couldn't find solutions online (it's hard to explain it without an example).

Say we have the following table:
RankMin Level
Rookie0
Soldier10
General100

<tbody>
</tbody>

Is there a formula (not vb) that would return the Rank based on an arbitrary Level? Examples:
f(2) = Rookie
f(10) = Soldier
f(99) = Soldier
f(5000) = General

I'm sorry if the solution is simple - I did a lot of googling, but couldn't find the answer.

Thank you!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Not sure what significance of f(?)

Try like...
Excel Workbook
ABCDEFGH
1LevelRank****Min LevelRank
22Rookie****0Rookie
310Soldier****10Soldier
45000General****100General
5********
6********
Sheet1



Hope that helps.
 
Upvote 0
Yes, that worked! I thought the lookup would only look at exact values, so thanks for teaching me the trick.

By the way, the f(x) were meant to be functions of x, a small math notation from where I'm from.

Thanks again!

Not sure what significance of f(?)

Try like...
Sheet1

*ABCDEFGH
1LevelRank****Min LevelRank
22Rookie****0Rookie
310Soldier****10Soldier
45000General****100General
5********
6********

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:89px;"><col style="width:126px;"><col style="width:22px;"><col style="width:22px;"><col style="width:22px;"><col style="width:22px;"><col style="width:87px;"><col style="width:80px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B2=IF(A2="","",LOOKUP(A2,$G$2:$G$4,H$2:$H$4))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Hope that helps.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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