Last row with a specific value.

[cata2200]

Hi

I'm working with two sheets: Sheet1 and Sheet2. First one provide me with several brief informations stored in Sheet2, based on the value from Sheet1!$A$1. Note that data stored in Sheet2 is increasing daily.
How can I get the last data from Sheet2!B:B, where has in column A the value from Sheet1!$A$1?

Thanks and regards,
Catalin

 

[pgc01]

Hi Catalin

You may have to adapt the range in Sheet2!B:B ( I considered th first 1000 rows).

Try:

<table><tr><td><table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</tr></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >1</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_10</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >2</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >3</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Result:</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_10</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >4</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table></td><td style="padding-left:5em;" ><table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</tr></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >1</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_01</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_01</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >2</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_02</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_02</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >3</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_03</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_03</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >4</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_04</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_04</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >5</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_05</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_05</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >6</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_06</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_06</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >7</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_07</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_07</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >8</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_08</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_08</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >9</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_09</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_09</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >10</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_10</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_10</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >11</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_11</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_11</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >12</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_12</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_12</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >13</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_13</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_13</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >14</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_14</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_14</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >15</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Value_15</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Name_15</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >16</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet2</td></tr></table></td></tr></table>
<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF " >Addr</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF " >Formula</td></tr><tr><td colspan=2 style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF " >[Book1]Sheet1</td></tr><tr><td rowspan=2 style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:center" >B3</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" > =INDEX(Sheet2!B:B,MAX(IF(Sheet2!A1:A1000=A1,ROW(B1:B1000)))) </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" > This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.</td></tr></table>

Hope this helps
PGC

 

[cata2200]

Great. Is working, with small modifications. Thank you very much.
Next step, if you can help me, is how to find ALL elements from Sheet2 where in A:A I will find the value Sheet1!$A$1.

(something like Select * for Sheet2!A:A=Sheet1!$A$1)

Thank you in advance for help
Catalin

 

[Domenic]

For the first part of your question, try...

=LOOKUP(2,1/(Sheet2!$A$1:$A$100=Sheet1!A1),Sheet2!$B$1:$B$100)

...confirmed with just ENTER. For the second part of your question, try...

B1:

=COUNTIF(Sheet2!$A$1:$A$100,Sheet1!A1)

C1, copied down (and across, if necessary):

=IF(ROWS(C$1:C1)<=$B$1,INDEX(Sheet2!B$1:B$100,SMALL(IF(Sheet2!$A$1:$A$100=Sheet1!$A$1,ROW(Sheet2!$A$1:$A$100)-ROW(Sheet2!$A$1)+1),ROWS(C$1:C1))),"")

...confirmed with CONTROL+SHIFT+ENTER. Also, if you're using Excel 2003 or a later version, convert your data into a list (Data > List > Create List). This way the ranges will automatically adjust as data is added/removed.

Hope this helps!