I have two tables. One table is has a column of zip codes and a factor associated with them, and another table containing a column of zips (this table has more zip codes than the first) and several columns of data within them. Zip Codes Factors.xlsZip Codes with Data.xls
What I want to do is look up the Zip codes in the first table (containing just the zips and a factor), and then I want to take the factor associated with that zip code, and multiply ALL of the existing data in that specific zip code row of the second table by that factor. This is just a sample data set, and the second table (the one with several columns) contains more zip codes than the table with just the zip code and the factor.
So, for example, take:
ZIP Code Factor
35004 1.2
from the first table and
Zip Codes Limit Factor 1 Limit Factor 2 Limit Factor 3 Limit Factor 4
35004 1.05 1 2 5
I want to be able to lookup the zip from the first table, find it in the second table, and have the results look like:
Zip Code Limit Factor 1 Limit Factor 2 Limit Factor 3 Limit Factor 4
35004 =1.2*1.5 =1.2*1 =1.2*2 =1.2*5
Is there a way to write a formula to do this? I know I could essentially do this with a VLOOKUP if I only had one zip code, but since I have a list of zip codes and multiple columns, I don't know how I would go about doing this. Any ideas would be greatly appreciated.
Thanks!!!
P.S. Ugh!!! The spacing got off in my example! Sorry everyone!! Here's a spreadsheet of what I would want using the first row of data:
Example Zip Result.PNG
I would like for the calculation to actually happen, I just didn't put an equals sign in for demonstration purposes.
Bookmarks