Hlookup values greater than

[rebeto13]

Hi I'm trying to have Excel return the last row's number of an array that's not necessarily organized.
What I have is an array with 1,2,3 and empty cells. The 1, 2 & 3 represent the start date, delayed or moved forward accordingly of an activity plan as well as the duration of the same. So as you might see its not precisely sorted.

For instance these could be rows in my array.
A B C D
1 1 1
2 3 1
3 2 1 1


What i want HLOOKUP to tell me is return the last row value of the array in the column where it finds the first value. If I use range lookup since its not sorted it returns the value of the column with the last value.

Do you know how could I solve this?

Thanks and regards,

 

[MrKowz]

Is this what you need?

<b>Excel 2003</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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date</td><td style=";">Qty1</td><td style=";">Qty2</td><td style=";">Qty3</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;">2</td><td style="text-align: right;;">1/1/2010</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Column:</td><td style=";">Qty2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2/1/2010</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Value:</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3/1/2010</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</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;">5</td><td style="text-align: right;;">4/1/2010</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4</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;;">5/1/2010</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><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>Worksheet 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">G3</th><td style="text-align:left">=INDEX(<font color="Blue">A1:D6,MATCH(<font color="Red">9.99E+307,INDEX(<font color="Green">A1:D6,,MATCH(<font color="Purple">G2,A1:D1,0</font>)</font>)</font>),MATCH(<font color="Red">G2,A1:D1,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[rebeto13]

Excel Workbook
	E	G	H	I	J	K	L
1	Project	J	F	M	A	M	J
188	Cost Plus	1.0	1.0	1.0	1.0	1.0	1.0
189	Product Returns				3.0	1.0	1.0
190	Potential New Offers Concept Test (TBC)		2.0	1.0	1.0
191		40553	40583	40613	40643	40673	40703
Brand Plan 2011

Excel 2002

See this is specifically what I have.
As you can see what you suggested won't work. Sorry for not being specific.
I'm trying to have excel tell me the value in the last row and the column with the first value in that specifc row... and then for every row...

 

[MrKowz]

Excel Workbook
	E	G	H	I	J	K	L
1	Project	J	F	M	A	M	J
188	Cost Plus	1.0	1.0	1.0	1.0	1.0	1.0
189	Product Returns				3.0	1.0	1.0
190	Potential New Offers Concept Test (TBC)		2.0	1.0	1.0
191		40553	40583	40613	40643	40673	40703
Brand Plan 2011

Excel 2002



See this is specifically what I have.
As you can see what you suggested won't work. Sorry for not being specific.
I'm trying to have excel tell me the value in the last row and the column with the first value in that specifc row... and then for every row...

I honestly have no idea what you are asking there. Would you be able to show me your desired output, and the logic behind said output?

 

[rebeto13]

Sure.
i initially had a formula that worked.
=IF(ISERROR(IF((HLOOKUP(1,$F76:$Q$191,T76,0)-TODAY())<0,"Target Date Past",IF(AND((HLOOKUP(1,$F76:$Q$191,T76,0)-TODAY())>0,(HLOOKUP(1,$F76:$Q$191,T76,0)-TODAY())<120),"Today +3M",""))),"",IF((HLOOKUP(1,$F76:$Q$191,T76,0)-TODAY())<0,"Target Date Past",IF(AND((HLOOKUP(1,$F76:$Q$191,T76,0)-TODAY())>0,(HLOOKUP(1,$F76:$Q$191,T76,0)-TODAY())<120),"Today +3M","")))

This worked when I was using 1 and empty cells.
The conflict started when we started using 2 & 3 also, to show that the initiation date had changed (either delayed or brought forward). So what I'm trying to have is 3 options. Leave empty cell ("") if the activity will start more than 120 days in the future.
Tell me activity is within 120 days ("Today +3M").
Or tell me target date is in the past.

So first step is that the formula tells me the value in the last row in the first column which is not empty, because this value is the first day of the month.

For instance in the case of row 189, i want the formula to return the value in J191, since 3 is the first value in that row. So if i did Hlookup for 3 (hlookup(3,$f$189:$q$191,3,0) it would work, but if I were to copy it in row 190 it wouldn't work because the first value is 2. And, if I do an hlookup (hlookup(3,$f$190:$q$191,3,1) enabling range lookup it returns the value in column J rather than H.

I don't know if its clear now.

 

[MrKowz]

Please confirm if these formulas return the desired dates used in your larger formula.

<b>Excel 2003</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 /><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><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFCC00;;">Project</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFCC00;;">J</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFCC00;;">F</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFCC00;;">M</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFCC00;;">A</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFCC00;;">M</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFCC00;;">J</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;color: #003366;background-color: #FFCC00;;">Date Returned (static last row)</td><td style="font-weight: bold;text-align: center;color: #003366;background-color: #FFCC00;;">Date Returned (dynamic last row)</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFFFFF;;">Cost Plus</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFFFFF;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFFFFF;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFFFFF;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFFFFF;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFFFFF;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFFFFF;;">1</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;">1/10/2011</td><td style="text-align: right;;">1/10/2011</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFFFFF;;">Product Returns</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFFFFF;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFFFFF;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFFFFF;;">1</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;">4/10/2011</td><td style="text-align: right;;">4/10/2011</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFFFFF;;">Potential New Offers Concept Test (TBC)</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFFFFF;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFFFFF;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #003366;background-color: #FFFFFF;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;">2/9/2011</td><td style="text-align: right;;">2/9/2011</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1/10/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2/9/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">3/11/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">4/10/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">5/10/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">6/9/2011</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet3</b></th></tr></td></thead></table><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">I2</th><td style="text-align:left">{=INDEX(<font color="Blue">$B$5:$G$5,,MATCH(<font color="Red">1,IF(<font color="Green">B2:G2<>"",1,""</font>),0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J2</th><td style="text-align:left">{=INDEX(<font color="Blue">B:G,MATCH(<font color="Red">9.99E+307,INDEX(<font color="Green">B:G,,MATCH(<font color="Purple">1,IF(<font color="Teal">B2:G2<>"",1,""</font>),0</font>)</font>)</font>),MATCH(<font color="Red">1,IF(<font color="Green">B2:G2<>"",1,""</font>),0</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 />