Looking up a value with and without leading 0-s from a table, XL2BB added

[Rxbxrt]

Hello,

Purpose of this is to enter a 5 digit number into cell A2, that number should be looked up from the ranges in table A4:B38, then according to which range the 5 digit number falls into, correct value (header "Zone") from C4:C38 must be returned to cell B3.

Also as seen from the table A4:B38, the 5 digit number can be with leading 0-s (for example 00001). How to solve it so that all 5 number digits either starting with a 0 or not can be found and the corresponding "Zone" number will be returned?

Terminalide kalkulaatorid 10.01.2025.xlsx
	A	B	C	D
1	Code	Zone
2	24999
3
4	Range start	Range end	Zone
5	00001	05999	1
6	06000	24999	2
7	25000	26299	4
8	26300	26999	2
9	27000	34999	4
10	35000	36999	3
11	37000	39999	4
12	40000	41999	3
13	42000	42499	4
14	42500	42699	3
15	42700	44099	4
16	44100	44199	3
17	44200	49999	4
18	50000	55199	3
19	55200	56399	4
20	56400	56499	3
21	56500	59999	4
22	60000	62099	3
23	62100	62999	4
24	63000	63099	3
25	63100	63999	4
26	64000	64599	3
27	64600	79999	4
28	80000	81999	3
29	82000	82099	4
30	82100	82399	3
31	82400	82999	4
32	83000	83099	3
33	83100	89999	4
34	90000	97499	2
35	97500	97599	4
36	97600	98299	2
37	98300	98499	4
38	98500	99999	2
39
PL HWL (2)

 

[Cubist]

Try:

=XLOOKUP(--A2,--A5:A38,C5:C38,,-1)

 

[Rxbxrt]

Try:

=XLOOKUP(--A2,--A5:A38,C5:C38,,-1)

This worked, thank you!