Excel VBA : Match the data from another workbook and return another column value

[jocquedemus]

So, i have a 'Summary' workbook that consists of zone, state, city, and total income. Inside one zone, there are a states. And inside the states, there are a cities. What i'm trying to do is to find the total income of each city in column D and summarize the total income of each state. To get the 'total income' value, i match it from the 'raw data' workbook. Here's the example :

To get the 'total income' value, i need to match the 'City' column in 'raw data' workbook (Column B) with the 'City' column in 'Summary' workbook (Column C). If the criteria is matched, it will return the value of 'Income' column in 'raw data' workbook (Column C) and summarize the income of each city. This is just an example, the real data has thousands of row so i need a program that run fast.
Any help would be greatly appreciated.

 

[danibapsilva]

You can do this using a simple VLOOKUP formula by matching the City and returning the column you want using {#,#,#,#}, the hashtags representing the number of the columns you want to return. 1 = column A, 2 = Column B etc... YouTube Video

 

[Peter_SSs]

You can do this using a simple VLOOKUP formula

I don't think so since, for example, city A1 appears several times in the raw data and VLOOKUP would only return the first value.

@jocquedemus
Welcome to the MrExcel board!

You will generally get faster and better help if you post your sample data with XL2BB as we can then easily copy it to test with.

Also, it looks like the figures in the INCOME column of raw data are numbers stored as text. It would be better if you were able to have those values as actual numbers as you could then use a SUMIF or SUMIFS function.

However, as it stands, see if this helps.

jocquedemus.xlsm
	B	C
1	City	INCOME
2	A1	3
3	A1	5
4	A3	2
5	A2	4
6	B1	8
7	B2	6
8	B2	5
9	A1	4
10	C1	4
11	C2	8
12	C1	9
raw data

Adjust the ranges in the formula to suit your data

jocquedemus.xlsm
	C	D
1	City	total income
2	A1	12
3	A2	4
4	A3	2
5	B1	8
6	B2	11
7	C1	13
8	C2	8
9	D1	0
Summary

Cell Formulas
Range	Formula
D2:D9	D2	=SUMPRODUCT(--('raw data'!B$2:B$100=C2),--'raw data'!C$2:C$100)