Better Ways Instead Using Lookup Value

[RudRud]

Hi All, I'm new here and im not a native here.

I got rules below that need to convert into formula.

I'm currently using lookup value (refer to B3 Formula) and i require to manual add my own statements in D2:F7 order to achieve the lookup value.

I'd like to know if got any other formula in more conveniece way?

Thank You ~

Book1
	A	B	C	D	E	F	G	H	I
1				My Formula Creation:
2				100	500	100%
3	20	0.6		90	99	90%
4	353.3333	1		80	89	80%
5	53.33333	0.6		70	79	70%
6	76.66667	0.7		0	69	60%
7
8
9
10				Rules:
11
12
13
14
15
16
17
18
19
20
21
Sheet1

Cell Formulas
Range	Formula
E3:E5	E3	=D2-1
B3:B6	B3	=LOOKUP(2,1/((A3>=$D$2:$D$6)*(A3<=$E$2:$E$6)),$F$2:$F$6)

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm not certain what you are asking, but a lookup table like you have is a good idea. You could try this bit simpler formula though.

23 02 26.xlsm
	A	B	C	D	E	F
1				My Formula Creation:
2				100	500	100%
3	20	60%		90	99	90%
4	353.3333333	100%		80	89	80%
5	53.33333333	60%		70	79	70%
6	76.66666667	70%		0	69	60%
XLOOKUP

Cell Formulas
Range	Formula
E3:E5	E3	=D2-1
B3:B6	B3	=XLOOKUP(A3,D$2:D$6,F$2:F$6,,-1)

 

[Peter_SSs]

You could try this bit simpler formula though.

.. or this one that will do all the lookups with a single formula in B3 only

For both of my suggestions, column E is not actually required, you just need the bottom value of each range & the relevant %

23 02 26.xlsm
	A	B	C	D	E	F
1				My Formula Creation:
2				100	500	100%
3	20	60%		90	99	90%
4	353.3333333	100%		80	89	80%
5	53.33333333	60%		70	79	70%
6	76.66666667	70%		0	69	60%
XLOOKUP

Cell Formulas
Range	Formula
B3:B6	B3	=XLOOKUP(A3:A6,D2:D6,F2:F6,,-1)
E3:E5	E3	=D2-1
Dynamic array formulas.

 

[RudRud]

.. or this one that will do all the lookups with a single formula in B3 only

23 02 26.xlsm
	A	B	C	D	E	F
1				My Formula Creation:
2				100	500	100%
3	20	60%		90	99	90%
4	353.3333333	100%		80	89	80%
5	53.33333333	60%		70	79	70%
6	76.66666667	70%		0	69	60%
XLOOKUP

Cell Formulas
Range	Formula
B3:B6	B3	=XLOOKUP(A3:A6,D2:D6,F2:F6,,-1)
E3:E5	E3	=D2-1
Dynamic array formulas.

Absolutely brilliant

Never thought that before, i dont need to manual fill the column E again in order to fit lookupvalue.

i have a lot situation like this, Again, Thanks so much fo your kind help

 

[Peter_SSs]

You are very welcome. Thanks for the follow-up.

 

[shinigamilight]

u don't necessarily need lookup for this just use boolean.

=MAX((A3>=$D$2:$D$6)*(A3<=$E$2:$E$6)*$F$2:$F$6)

 

[RudRud]

u don't necessarily need lookup for this just use boolean.

=MAX((A3>=$D$2:$D$6)*(A3<=$E$2:$E$6)*$F$2:$F$6)

Thanks for sharing and kind assist