HELP! Returning Value between 1 and 3

[quikieemart]

Hello,

It would be great if anyone could help me =]

So sheet 1 has a list of chemical codes and next to each chemical code shows its corresponding quanityt. I want cell A2 in sheet 2 to look at A1 of sheet 2 (a specific year: 2007), and if this is the correct year then I want A2 of sheet 2 to look at A2:B4 of sheet 1 and return the chemical name (A2:A4 sheet 1) ONLY if its corresponding quantity (B2:B4) is between 1 and 3: (1 <= qty < 3)

EX:
(SHEET 2)
A
1 2007
2 =function

(SHEET 1)
A B
1 chemical quantity
2 510 A 2.1
3 53 4.3
4 77526 1.83

I have this so far: (SHEET 2: A2)

=IF(AND(IF($A$1=2007,'SHEET 1'!$A2),('SHEET 1'!$B2)>=1),'SHEET 1'!$A2)

I tried to add another 'and' function in there to have a return that's less than quantity 3, but I couldn't figure it out.


Thanks,
Huan

 

[Special-K99]

In your example of Sheet1 you have two quantities that are between 1 and 3, ie Row 2 (value 2.1) and Row 4 (Value 1.83).
I know you want column A of this sheet returned but which of these two rows do you want returned as both values satisfy your condition?

 

[shift-del]

Hello

<rows($a$2:a2),"",index(tabelle1!$a$2:$a$4,small(if((tabelle1!$b$2:$b$4><rows($a$2:a2),"",index(tabelle1!$a$2:$a$4,small(if((tabelle1!$b$2:$b$4><table valign="middle" colspan="2" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="60pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="2" align="middle">Worksheet 'Sheet2'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="right">2007</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td align="right">510</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">3</td><td align="right">77526</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">4</td><td align="left">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">5</td><td align="left">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">6</td><td align="left">
</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: fixed; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Cell</td><td>Formula</td></tr><tr><td>A2</td><td>{=IF($A$1=2007,IF(SUM((Tabelle1!$B$2:$B$4<3)*(Tabelle1!$B$2:$B$4>1))<ROWS($A$2:A2),"",INDEX(Tabelle1!$A$2:$A$4,SMALL(IF((Tabelle1!$B$2:$B$4<3)*(Tabelle1!$B$2:$B$4>1),ROW($1:$3)),ROWS($A$2:A2)))),"")}</td></tr></tbody></table><table style="font-family: Arial; font-size: 8pt; background-color: rgb(255, 255, 255);"><tbody><tr><td style="font-weight: bold;">Array formula!
</td></tr><tr><td>Do not enter the curly brackets {}.</td></tr><tr><td>Enter the formular with CTRL-SHIFT-ENTER instead of just ENTER.
</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Created and displayed with Tab2HTML (v2.4.1). ©Gerd alias Bamberg</td></tr></tbody></table>
</rows($a$2:a2),"",index(tabelle1!$a$2:$a$4,small(if((tabelle1!$b$2:$b$4></rows($a$2:a2),"",index(tabelle1!$a$2:$a$4,small(if((tabelle1!$b$2:$b$4>

 

[quikieemart]

Oh, I'm sorry I forgot to mention. If possible, I want all the values between 1 and 3 to be listed. So Sheet 2 will have a column (A2, A3, A4, etc.) of all the chemicals with quantities between 1 and 3 taken from Sheet 1.

Let's say there are more chemicals after A4 of Sheet 1. The functions in Sheet 2 will skip over any chemicals that are not between 1 and 3 and return the next possible chemical WITHOUT skipping a cell in Sheet 2. I'm not sure if this is possible, asking too much? :X .

I will create another example if needed.

Thanks for the quick reply btw !