it doesn't work for 80?
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) |
for 80 should say "not found"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"
why you are using column A i don't need it at all.
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 |
AWESOME, you are the best .it works like a charm.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.
This is the best web site i have ever seen ,it is awesome.