How to get data out of it?

[Ahmed Fawad]

from this data:
I need to work out is....
1. if i enter Product code it search out the min price against that code in column 2 its i have done by formula MIN(IF) bla bla

2. Now the next is ... i need to have if i enter product code ...

first it get the min price against product # then show the order ref. and order date in same row...

if anyone can help with this?


AFB

 

[Andrew Poulsom]

What formula are you using to find the MIN?

 

[Ahmed Fawad]

What formula are you using to find the MIN?

we entered product # in E1 n formula in F1
then
=MIN(IF(A2:A13=E1,B2:B13))
ctrl+Enter

 

[Andrew Poulsom]

Try:

=INDEX(C2:C13,MATCH(MIN(IF(A2:A13=E1,B2:B13)),IF(A2:A13=E1,B2:B13),FALSE))

=INDEX(D2:D13,MATCH(MIN(IF(A2:A13=E1,B2:B13)),IF(A2:A13=E1,B2:B13),FALSE))

confirmed with Ctrl+Shift+Enter.

 

[Ahmed Fawad]

Try:

=INDEX(C2:C13,MATCH(MIN(IF(A2:A13=E1,B2:B13)),IF(A2:A13=E1,B2:B13),FALSE))

=INDEX(D2:D13,MATCH(MIN(IF(A2:A13=E1,B2:B13)),IF(A2:A13=E1,B2:B13),FALSE))

confirmed with Ctrl+Shift+Enter.

Not working.....

Too few arguments for this function.

 

[Andrew Poulsom]

Not working.....

Too few arguments for this function.

Please try again. Copy the formula from my reply and paste it into your worksheet.

 

[Ahmed Fawad]

Please try again. Copy the formula from my reply and paste it into your worksheet.

Check this out mate, here its saying Error in value

 

[Andrew Poulsom]

The formula works perfectly for me. Is part of the formula highlighted when you get the error?

 

[Ahmed Fawad]

I don't see error highlight.... if u can help with this...


AFB

 

[Haseeb Avarakkan]

Hello, Try,

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Product</td><td style="font-weight: bold;text-align: center;;">Price</td><td style="font-weight: bold;text-align: center;;">Order #</td><td style="font-weight: bold;text-align: center;;">Order Date</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Lookup Product</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">12</td><td style="text-align: right;;">128</td><td style="text-align: right;;">54</td><td style="text-align: right;;">10-Jan-10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">15</td><td style="text-align: right;;">256</td><td style="text-align: right;;">57</td><td style="text-align: right;;">11-Jan-10</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">MIN Price</td><td style="font-weight: bold;text-align: center;;">Order #</td><td style="font-weight: bold;text-align: center;;">Order Date</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">18</td><td style="text-align: right;;">126</td><td style="text-align: right;;">59</td><td style="text-align: right;;">25-May-10</td><td style="text-align: right;;"></td><td style="text-align: right;;">110</td><td style="text-align: right;;">57</td><td style="text-align: right;;">2-Jan-10</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">15</td><td style="text-align: right;;">244</td><td style="text-align: right;;">50</td><td style="text-align: right;;">16-Apr-10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">16</td><td style="text-align: right;;">269</td><td style="text-align: right;;">67</td><td style="text-align: right;;">9-Feb-10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">17</td><td style="text-align: right;;">258</td><td style="text-align: right;;">94</td><td style="text-align: right;;">6-Mar-10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">12</td><td style="text-align: right;;">110</td><td style="text-align: right;;">57</td><td style="text-align: right;;">2-Jan-10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">15</td><td style="text-align: right;;">159</td><td style="text-align: right;;">44</td><td style="text-align: right;;">3-Apr-10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">147</td><td style="text-align: right;;">42</td><td style="text-align: right;;">13-Mar-10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">156</td><td style="text-align: right;;">27</td><td style="text-align: right;;">10-Jun-10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">10</td><td style="text-align: right;;">249</td><td style="text-align: right;;">28</td><td style="text-align: right;;">5-Jun-10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">13</td><td style="text-align: right;;">198</td><td style="text-align: right;;">34</td><td style="text-align: right;;">8-Feb-10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F4</th><td style="text-align:left">{=MIN(<font color="Blue">IF(<font color="Red">A2:A13=G1,B2:B13</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G4</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">C$2:C$13,MATCH(<font color="Green">1,IF(<font color="Purple">$A$2:$A$13=$G$1,IF(<font color="Teal">$B$2:$B$13=$F4,1</font>)</font>),0</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H4</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">D$2:D$13,MATCH(<font color="Green">1,IF(<font color="Purple">$A$2:$A$13=$G$1,IF(<font color="Teal">$B$2:$B$13=$F4,1</font>)</font>),0</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />