lookup with repeating data in column a

Are you trying to retrieve the sum of the corresponding data? A count of how many times the account number appears in the first column? The corresponding data for just the first instance of that account number?

Let us know, we'll be better able to assist.

Thanks.

If you're looking to sum the number of units by Account #, based on my example below:

Please Login or Register  to view this content.

In D2 you would enter the formula:
=SUMPRODUCT(--($A$2:$A$8=1000),$B$2:$B$8)

For the other accounts, you could change the "=1000" part to "=2000", "=3000", etc..

If this is not what you're looking for, upload an example spreadsheet (zipped) so we can take a look. Add any detailed notes so we can understand exactly what you're looking to do and where you looking to pull data from.

Oops, forgot to mention that i want to pull the data from this worksheet onto another one. I'd love to be able to pull the total units only for each account number listed on the main sheet to a cell on another spreadsheet. i don't think i can do this using subtotals...using example below, i'd like to pull the number of units totaled that account number 1000 bought onto another spreadsheet.

Account Number - Units - Name
1000 - 6 - Smith
2000 - 6 - Jones
3000 - 7 - Bennett
1000 - 6 - Smith
1000 - 5 - Smith
2000 - 4 - Jones


etc...

All you need to do is reference the other sheet name in your formula. For example, in sheet 2 (any cell) enter:

=SUMPRODUCT(--(Sheet1!$A$2:$A$8=1000),Sheet1!$B$2:$B$8)

Again, to reference the other 'accounts', just change the 1000 to 2000, 3000, etc.