#### EvansRJ

##### New Member
I have 2 spreadsheets open and here is what I am trying to do. Let say Sheet A lists serial numbers (F1:F100) but no store ID. Sheet B has the serial numbers (A1-A300) and store ID (C1:C300) but also much more info I don't need. I want to search for the serial numbers listed in Sheet A on Sheet B and have it return the store ID's in a new column (D1:D100) on Sheet A. Make sense?

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Eric W

##### MrExcel MVP
Try putting this in D1 on sheet A:

=VLOOKUP(F1,'Sheet B'!\$A\$1:\$C\$300,3,0)

and copy down.

#### EvansRJ

##### New Member
Try putting this in D1 on sheet A:

=VLOOKUP(F1,'Sheet B'!\$A\$1:\$C\$300,3,0)

and copy down.
That allowed me to do what I wanted. Thanks.

Second part. Now that I have the serial numbers and store ID's, I need to consolidate. Column D (2:2369) contains \$ values, Column E contains the serial #'s, and Column I contains the store ID's. There are multiple cells with the same store ID. I want to SUM the \$ values for each row containing the same store ID and only report 1 store ID. For the serial # column, can I have it simply report the # of serial numbers consolidated?

Thanks.

#### Eric W

##### MrExcel MVP
I'm having a hard time picturing your sheet, since it doesn't seem to jibe with your first post. Nevertheless, it seems like you want the SUMIF function. Consider this:

Book1
ABCDEF
1Serial #SumSerial #Amt
211311
321232
453
514
625
756
827
918
10
Sheet7
Cell Formulas
RangeFormula
B2:B3B2=SUMIF(\$E\$2:\$E\$9,A2,\$F\$2:\$F\$9)

Incidentally, I used the XL2BB tool, which makes it easier to show what you want. Check the download button on the right of the response box.

Replies
2
Views
304
Replies
2
Views
471
Replies
1
Views
195
Replies
0
Views
106
Replies
0
Views
376

1,147,482
Messages
5,741,404
Members
423,657
Latest member
Medrok2021

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