Please help!!

[EvansRJ]

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?

 

[Eric W]

Try putting this in D1 on sheet A:

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

and copy down.

 

[EvansRJ]

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]

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
	A	B	C	D	E	F
1	Serial #	Sum			Serial #	Amt
2	1	13			1	1
3	2	12			3	2
4					5	3
5					1	4
6					2	5
7					5	6
8					2	7
9					1	8
10
Sheet7

Cell Formulas
Range	Formula
B2:B3	B2	=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.