Please help!!

EvansRJ

New Member
Joined
May 10, 2011
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
11,655
Try putting this in D1 on sheet A:

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

and copy down.
 

EvansRJ

New Member
Joined
May 10, 2011
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
11,655
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top