if question

[dkinnison]

14 20
12 25
10 35
8 50
4 65

I HAVE A CELL AT LET SAY E5 = TO 30
THE NEXT NUMBER UP IS 35 AND I WANT
TO DISPLAY THE 10.

IF E5 = TO 55 THAN I WANT IT TO DISPLAY
4

WHAT IS THE FORMULA FOR THIS?

MY EMAIL IS dkinniso@dyag-eng.com
or dkinnison@voyager.net

Thanks


Daniel Kinnison
Dyag Automation Group
10489 Grand River, Suite E
Brighton, Mich. 48116
PH: 810.229.7900 EX:114

 

[Aladin Akyurek]

<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=7><FONT COLOR=WHITE>Microsoft Excel - HtmlMaker1.10.xls</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=7>File(<U>F</U>) Edit(<U>E</U>) View(<U>V</U>) Insert(<U>I</U>) Tool(<U>T</U>) Data(<U>D</U>) Window(<U>W</U>) Help(<U>H</U>)</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>A1</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=4 BGCOLOR=White>Nums1</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>E</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>F</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#008080 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FFFFFF>Nums1</FONT></TD><TD BGCOLOR=#008080 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FFFFFF>Nums2</FONT></TD><TD BGCOLOR=#FFFFFF></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>30</FONT></TD><TD BGCOLOR=#FF9900 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=OFFSET($A$2,IF(COUNTIF(B$1:B$5,D1),MATCH(D1,B$1:B$5,0),MATCH(D1,B$1:B$5))-1,0,1,1)')><FONT FACE=MS P???? COLOR=#000000>10</FONT></A></TD><TD BGCOLOR=#FF9900 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=INDEX(A$1:A$5,IF(COUNTIF(B$1:B$5,D1),MATCH(D1,B$1:B$5,0),MATCH(D1,B$1:B$5)+1))')><FONT FACE=MS P???? COLOR=#000000>10</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#FF9900 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FFFFFF>12</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FFFFFF>25</FONT></TD><TD BGCOLOR=#FFFFFF></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>55</FONT></TD><TD BGCOLOR=#FF9900 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=OFFSET($A$2,IF(COUNTIF(B$1:B$5,D2),MATCH(D2,B$1:B$5,0),MATCH(D2,B$1:B$5))-1,0,1,1)')><FONT FACE=MS P???? COLOR=#000000>4</FONT></A></TD><TD BGCOLOR=#FF9900 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=INDEX(A$1:A$5,IF(COUNTIF(B$1:B$5,D2),MATCH(D2,B$1:B$5,0),MATCH(D2,B$1:B$5)+1))')><FONT FACE=MS P???? COLOR=#000000>4</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#FF9900 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FFFFFF>10</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FFFFFF>35</FONT></TD><TD BGCOLOR=#FFFFFF></TD><TD BGCOLOR=#FFFFFF></TD><TD BGCOLOR=#FFFFFF></TD><TD BGCOLOR=#FFFFFF></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#FF9900 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FFFFFF>8</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FFFFFF>50</FONT></TD><TD BGCOLOR=#FFFFFF></TD><TD BGCOLOR=#FFFFFF></TD><TD BGCOLOR=#FFFFFF></TD><TD BGCOLOR=#FFFFFF></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#FF9900 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FFFFFF>4</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FFFFFF>65</FONT></TD><TD BGCOLOR=#FFFFFF></TD><TD BGCOLOR=#FFFFFF></TD><TD BGCOLOR=#FFFFFF></TD><TD BGCOLOR=#FFFFFF></TD></TR><TR><TD COLSPAN=7><U>Sheet1</U></TD></TR></TABLE><FONT COLOR=#339966>You can see the formula of cells only click each above hyperlinks</FONT><FONT COLOR=blue SIZE=1>The above image was automatically written by excel VBA.</FONT><FONT COLOR=blue SIZE=1>If you want this code,<A HREF=mailto:corosuke@chan.co.jp>click here</A> and I'll email the file to you.</FONT></CENTER>

Here are 2 sets of formulas to achieve the retrieval task that you formulated:

In E1:

=OFFSET($A$2,IF(COUNTIF(B$1:B$5,D1),MATCH(D1,B$1:B$5,0),MATCH(D1,B$1:B$5))-1,0,1,1)

In F1:

=INDEX(A$1:A$5,IF(COUNTIF(B$1:B$5,D1),MATCH(D1,B$1:B$5,0),MATCH(D1,B$1:B$5)+1))

which also can be seen by activating the hyperlinks in these cells [ Created by HtmlMaker, designed by Colo. My deeply felt thanks for that ].


Aladin
This message was edited by Aladin Akyurek on 2002-05-10 05:02
This message was edited by Aladin Akyurek on 2002-05-10 08:03