# Last row with a specific value.

#### cata2200

##### Board Regular
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

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### pgc01

##### MrExcel MVP
Hi Catalin

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

Try:

Hope this helps
PGC

#### cata2200

##### Board Regular
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

##### MrExcel MVP
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!

Replies
0
Views
183
Replies
0
Views
178
Replies
3
Views
206
Replies
3
Views
678
Replies
3
Views
472

1,181,719
Messages
5,931,656
Members
436,797
Latest member
fullmetalpogi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back