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?

 

[Andrew Poulsom]

it doesn't work for 80?

For 80 the formula returns f:

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

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

 

[coxixx]

For 80 the formula returns f:

Excel 2010

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

<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>

for 80 should say "not found"

 

[Aladin Akyurek]

for 80 should say "not found"

Either...

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

Or...

=IF(D1 < MIN($A$1:$A$6),"not Found",IF(D1 > MAX($B$1:$B$6),"not found",LOOKUP(D1,$A$1:$A$6,$C$1:$C$6)))

Otherwise, post #5.

See: https://dl.dropboxusercontent.com/u/65698317/coxixx%20lookup.xlsx

 

[coxixx]

why you are using column A i don't need it at all.

 

[Aladin Akyurek]

why you are using column A i don't need it at all.

Trying to help can be hard at times...

10	20	a	5	Not Found	a	not Found
21	30	b	30	b	b	b
31	40	c	31	c	c	c
41	50	d	55	e	e	e
51	60	e	80	Not Found	f	not found
61	70	f	70	f	f	f
			23	b	b	b

<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6343" width=178><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2929" width=82><TBODY>
</TBODY>

The data is in B1:D6; the values to look up in E1:E6...

F1, control+shift+enter and copy down:

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

G1, just enter and copy down:

=LOOKUP(IF(E1 < MIN($B$1:$B$6),MIN($B$1:$B$6),E1),$B$1:$B$6,$D$1:$D$6)

H1, just enter and copy down:

=IF(E1<MIN($B$1:$B$6),"not Found",IF(E1>MAX($C$1:$C$6),"not found",LOOKUP(E1,$B$1:$B$6,$D$1:$D$6)))

 

[coxixx]

Trying to help can be hard at times...

10	20	a	5	Not Found	a	not Found
21	30	b	30	b	b	b
31	40	c	31	c	c	c
41	50	d	55	e	e	e
51	60	e	80	Not Found	f	not found
61	70	f	70	f	f	f
			23	b	b	b

<tbody>
</tbody>

The data is in B1:D6; the values to look up in E1:E6...

F1, control+shift+enter and copy down:

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

G1, just enter and copy down:

=LOOKUP(IF(E1 < MIN($B$1:$B$6),MIN($B$1:$B$6),E1),$B$1:$B$6,$D$1:$D$6)

H1, just enter and copy down:

=IF(E1<min($b$1:$b$6),"not found",if(e1="">MAX($C$1:$C$6),"not found",LOOKUP(E1,$B$1:$B$6,$D$1:$D$6)))

AWESOME, you are the best .it works like a charm.
thank you so much for your help and time.</min($b$1:$b$6),"not>

 

[coxixx]

This is the best web site i have ever seen ,it is awesome.

 

[Aladin Akyurek]

AWESOME, you are the best .it works like a charm.
thank you so much for your help and time.

You are welcome.

This is the best web site i have ever seen ,it is awesome.

Thanks for the nice feedback.