lookup function Doesn't work well

[coxixx]

A	B	c	d	e
1	10	20	a	5	=lookup(e1,B1:C6,d1:d6)
2	21	30	b	30
3	31	40	c	31
4	41	50	d	55
5	51	60	e	80
6	61	70	f	70

<tbody>
</tbody>

this lookup function doesn't work well ?does anyone can help?

 

[Aladin Akyurek]

Would you explain what your objective is? If E1 = 15, what is the result you expect, and what if E1 = 23?

 

[coxixx]

if e1 is between range b1 and c1 return d1

 

[coxixx]

for e=23 should returns b

 

[Aladin Akyurek]

if e1 is between range b1 and c1 return d1

for e=23 should returns b

F1, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($C$1:$C$6,MATCH(1,IF(D1>=$A$1:$A$6,
  IF(D1<=$B$1:$B$6,1)),0)),"Not Found")

 

[Andrew Poulsom]

If E1 is 23 your formula returns b:

Excel 2010
	A	B	C	D	E	F
1	1	10	20	a	23	b
2	2	21	30	b	30
3	3	31	40	c	31
4	4	41	50	d	55
5	5	51	60	e	80
6	6	61	70	f	70
Sheet4

Cell Formulas
Range	Formula
F1	=LOOKUP(E1,B1:B6,D1:D6)

 

[coxixx]

If E1 is 23 your formula returns b:

Excel 2010

	A	B	C	D	E	F
1	1	10	20	a	23	b
2	2	21	30	b	30
3	3	31	40	c	31
4	4	41	50	d	55
5	5	51	60	e	80
6	6	61	70	f	70

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula F1 =LOOKUP(E1,B1:B6,D1:D6) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

it doesn't work for 80?

 

[Aladin Akyurek]

it doesn't work for 80?

How about post #5?

 

[coxixx]

it returns not found for all of them

 

[Aladin Akyurek]

it returns not found for all of them

You need to apply control+shift+enter to the formula at the same time, not just enter.

By the way, if your table is in ascending order in A1:A6, perhaps just:

E1, just enter and copy down:

=LOOKUP(IF(D1 < MIN($A$1:$A$6), MIN($A$1:$A$6), D1), $A$1:$A$6, $C$1:$C$6)