Look up a value to assign a label

Question

Is there a function I can use to look up a value and assign a label if it's less then another value; for example, I have a list of numbers, with an assigned label and a number I want to search.&nbsp;Values between GE 0 and LE 5 should come back with a label of 0005er.&nbsp; Values between GT 5 and LE 20 should come back with a label of 0020er. Values between GT 20 and LE 50 should return 0050er, etc.0, .14, 5 should all return label 0005er5.1, 7. 19.5, 20 should all return label 0020er&nbsp;Upper Value&nbsp;&nbsp; Label5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0005er20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0020er50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0050er

snowman55 · Answer

bma1412&nbsp;If you have a recent version of Excel (2021 or later, I believe), this can be accomplished with the XLOOKUP function, specifying a match_mode argument of 1.&nbsp; For details and alternative solutions, see the attached workbook.&nbsp;

bma1412 · Answer

Thanks for your response. How would I set up the xlookup function if this were the table:
MP Levels MP Level
5 0005
20 0020
50 0050
100 0100
200 0200
300 0300
500 0500
750 0750
1,000 1000
1,500 1500
2,500 2500
3,500 3500
5,000 5000
7,500 7500
And I wanted to lookup a value of 3445 which resides is cell a45?

I tried xlookup but got an error response so I must not be using the function properly.

bma1412 · Answer

I figured out xlookup. That was the solution I was looking for. Thanks!!

Forum Discussion

Look up a value to assign a label

3 Replies

Resources