+ Reply to Thread
Results 1 to 2 of 2

Multiplying Existing Table by a Factor Using a Lookup

  1. #1
    Registered User
    Join Date
    08-28-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    7

    Multiplying Existing Table by a Factor Using a Lookup

    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.
    Last edited by clueless12; 08-28-2015 at 05:51 PM.

  2. #2
    Registered User
    Join Date
    08-28-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    7

    Re: Multiplying Existing Table by a Factor Using a Lookup

    In addition, I'd like to do this for all zip codes in the first table. I'm not quite sure I made that clear. I'm not quite sure if a macro would be the way to go, but like I said it's complicated with so many things changing.... I hope I was able to be clear with what I want. If not, I'm sorry and will do my best to clarify!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Factor Up and Factor Down Values Formula
    By alive555 in forum Excel General
    Replies: 6
    Last Post: 08-14-2015, 12:01 PM
  2. Replies: 4
    Last Post: 07-28-2014, 09:12 AM
  3. [SOLVED] Multiple lookup adding columns, then multiplying, then lookup, add, and subtract
    By mamig in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-08-2013, 12:55 AM
  4. [SOLVED] Two Factor permutation Table- question about past post for shg
    By david.herrera1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-12-2012, 10:22 PM
  5. Adding lookup table to existing spreadsheet
    By bellicusa in forum Excel General
    Replies: 1
    Last Post: 01-18-2012, 12:21 PM
  6. Excel 2007 : Vlookup for additional factor in the table
    By andy9988 in forum Excel General
    Replies: 9
    Last Post: 09-17-2010, 11:26 AM
  7. Pivot table data in preparation for two factor ANOVA
    By ecramer in forum Excel General
    Replies: 0
    Last Post: 05-14-2010, 05:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1