Results 1 to 8 of 8

Thread: Excel Formula Help

  1. #1
    Senior Member
    Join Date
    Jul 2003
    Location
    Nott'm, East Midlands
    Posts
    1,954
    Thanks
    0
    Thanked
    0 times in 0 posts

    Excel Formula Help

    I've got a set of data in Excel that I want to fill in with data from another tab via VLOOKUP. Easy I hear you say, but I want to use two different values to VLOOKUP from.

    For example,

    I want to filter on a product, for arguments sake let's call it 'MOBO' and how many we sold on a particular date.

    So my raw data will look something like the below

    Code:
    	             COLUMN1 COLUMN2 COLUMN3   COLUMN4	Total
    
    MOBO	01-Aug-06	430	466	40	1	937
    MOBO	02-Aug-06	430	466	40	1	937
    MOBO	03-Aug-06	430	466	40	1	937
    MOBO	04-Aug-06	430	466	40	1	937
    MOBO	05-Aug-06	430	466	40	1	937
    MOBO	06-Aug-06	430	466	40	1	937
    MOBO	07-Aug-06	428	455	40	1	924
    MOBO	08-Aug-06	427	454	40	1	922
    MOBO	09-Aug-06	425	453	40	1	919
    MOBO	10-Aug-06	425	453	40	1	919
    MOBO	11-Aug-06	425	452	40	1	918
    MOBO	12-Aug-06	425	452	40	1	918
    MOBO	13-Aug-06	425	452	40	1	918
    MOBO	14-Aug-06	420	402	40	1	863
    MOBO	15-Aug-06	420	402	40	1	863
    MOBO	16-Aug-06	419	399	40	1	859
    MOBO	17-Aug-06	419	399	40	1	859
    MOBO	18-Aug-06	419	398	40	1	858
    MOBO	19-Aug-06	418	398	40	1	857
    MOBO	20-Aug-06	418	398	40	1	857
    MOBO	21-Aug-06	414	364	40	1	819
    MOBO	22-Aug-06	413	364	40	1	818
    MOBO	23-Aug-06	413	364	40	1	818
    MOBO	24-Aug-06	413	364	40	1	818
    MOBO	25-Aug-06	412	364	40	1	817
    MOBO	26-Aug-06	411	364	40	1	816
    MOBO	27-Aug-06	411	364	40	1	816
    MOBO	28-Aug-06	407	341	40	1	789
    MOBO	29-Aug-06	407	341	40	1	789
    MOBO	30-Aug-06	407	341	40	1	789
    MOBO	31-Aug-06	406	341	40	1	788

    As you can see the data fluctuates on a day to day basis. Now, I want to reflect this data on another tab via a LOOKUP query.

    How do I do this? The data on the other tab is also split by dates. So therefore I need to use a VLOOKUP query using the date and 'MOBO'?

    Thanks for any help fellas

  2. #2
    Nothing runs like a Deere cotswoldcs's Avatar
    Join Date
    Mar 2004
    Location
    Bang in the heart of the cotswolds
    Posts
    793
    Thanks
    40
    Thanked
    18 times in 18 posts
    • cotswoldcs's system
      • Motherboard:
      • Asus P4C800-E Deluxe
      • CPU:
      • Pentium 4 3.0Ghz Northwood (@3.4Ghz on water)
      • Memory:
      • 1.5Gb Corsair TwinX PC3200/PC3700
      • Storage:
      • 150Gb Raptor
      • Graphics card(s):
      • XFX 6600GT 128Mb (w waterblock)
      • PSU:
      • Seasonic S12-380
      • Case:
      • Antec Sonata I
      • Monitor(s):
      • 3 x Iiyama 19" LCD (5:4) on Comrac Tripple Mount
      • Internet:
      • PlusNet 1.6Mb ADSL
    I think a PivotTable would solve your problem. Have you used the technique before?

  3. #3
    HEXUS.social member Allen's Avatar
    Join Date
    Nov 2003
    Location
    Brighton
    Posts
    8,536
    Thanks
    363
    Thanked
    262 times in 168 posts
    • Allen's system
      • Motherboard:
      • ASUS Maximus VIII Gene
      • CPU:
      • Intel Core i5 6600K
      • Memory:
      • 2 x 8GB Kingston HyperX Predator DDR4-3000
      • Storage:
      • 256GB Samsung 950 PRO NVMe M.2 (OS) + 2 x 512GB Samsung 960 EVO in RAID 0 (Games)
      • Graphics card(s):
      • ASUS ROG Strix GeForce GTX 1080 Ti OC
      • PSU:
      • XFX P1-650X-NLG9 XXX 650W Modular
      • Case:
      • Fractal Design Node 804
      • Operating System:
      • Windows 10 Home 64-bit
      • Monitor(s):
      • 27" BenQ XL2730Z + 23" Dell U2311H
      • Internet:
      • Virgin Media 200Mbps
    I'm a bit confused with why you are wanting to do this. A simple Autofilter will help, surely?

    Or am I not reading your question correctly? Could you maybe give a bit more info exactly what you have and what you want?

  4. #4
    Member
    Join Date
    Aug 2005
    Location
    Lancashire, UK
    Posts
    145
    Thanks
    0
    Thanked
    0 times in 0 posts
    Whenever I've needed to do this I've had to add a column to each table which is a concatonation of the items I need to look-up. That's the limit of Excel's lookups.

  5. #5
    Senior Member
    Join Date
    Jul 2003
    Location
    Nott'm, East Midlands
    Posts
    1,954
    Thanks
    0
    Thanked
    0 times in 0 posts
    I have 2 tabs in an excel spreadsheet, one is the raw data (below):

    Code:
    TYPE        DATE             COLUMN1 COLUMN2 COLUMN3 COLUMN4 TOTAL
    MOBO	01-Aug-06	430	466	40	1	937
    MOBO	02-Aug-06	430	466	40	1	937
    MOBO	03-Aug-06	430	466	40	1	937
    MOBO	04-Aug-06	430	466	40	1	937
    MOBO	05-Aug-06	430	466	40	1	937
    MOBO	06-Aug-06	430	466	40	1	937
    MOBO	07-Aug-06	428	455	40	1	924
    MOBO	08-Aug-06	427	454	40	1	922
    MOBO	09-Aug-06	425	453	40	1	919
    MOBO	10-Aug-06	425	453	40	1	919
    MOBO	11-Aug-06	425	452	40	1	918
    MOBO	12-Aug-06	425	452	40	1	918
    MOBO	13-Aug-06	425	452	40	1	918
    MOBO	14-Aug-06	420	402	40	1	863
    MOBO	15-Aug-06	420	402	40	1	863
    MOBO	16-Aug-06	419	399	40	1	859
    MOBO	17-Aug-06	419	399	40	1	859
    MOBO	18-Aug-06	419	398	40	1	858
    MOBO	19-Aug-06	418	398	40	1	857
    MOBO	20-Aug-06	418	398	40	1	857
    MOBO	21-Aug-06	414	364	40	1	819
    MOBO	22-Aug-06	413	364	40	1	818
    MOBO	23-Aug-06	413	364	40	1	818
    MOBO	24-Aug-06	413	364	40	1	818
    MOBO	25-Aug-06	412	364	40	1	817
    MOBO	26-Aug-06	411	364	40	1	816
    MOBO	27-Aug-06	411	364	40	1	816
    MOBO	28-Aug-06	407	341	40	1	789
    MOBO	29-Aug-06	407	341	40	1	789
    MOBO	30-Aug-06	407	341	40	1	789
    MOBO	31-Aug-06	406	341	40	1	788
    Now, I have many sets of the raw data on a raw data tab above (e.g. CPU, PSU where it says MOBO above) and you are not guaranteed to get MOBO in the raw data, so the data is not static in this tab. I.e. I can't just put a formula like =A142, as cell A142 next time will be a different date (30 and 31 day months for example).

    The tab I want it showing on in Excel needs to look like this:
    Code:
    DATE             COLUMN1 COLUMN2 COLUMN3 COLUMN4 TOTAL
    01-Aug-06	430	466	40	1	937
    02-Aug-06	430	466	40	1	937
    03-Aug-06	430	466	40	1	937
    04-Aug-06	430	466	40	1	937
    05-Aug-06	430	466	40	1	937
    06-Aug-06	430	466	40	1	937
    07-Aug-06	428	455	40	1	924
    08-Aug-06	427	454	40	1	922
    09-Aug-06	425	453	40	1	919
    10-Aug-06	425	453	40	1	919
    11-Aug-06	425	452	40	1	918
    12-Aug-06	425	452	40	1	918
    13-Aug-06	425	452	40	1	918
    14-Aug-06	420	402	40	1	863
    15-Aug-06	420	402	40	1	863
    16-Aug-06	419	399	40	1	859
    17-Aug-06	419	399	40	1	859
    18-Aug-06	419	398	40	1	858
    19-Aug-06	418	398	40	1	857
    20-Aug-06	418	398	40	1	857
    21-Aug-06	414	364	40	1	819
    22-Aug-06	413	364	40	1	818
    23-Aug-06	413	364	40	1	818
    24-Aug-06	413	364	40	1	818
    25-Aug-06	412	364	40	1	817
    26-Aug-06	411	364	40	1	816
    27-Aug-06	411	364	40	1	816
    28-Aug-06	407	341	40	1	789
    29-Aug-06	407	341	40	1	789
    30-Aug-06	407	341	40	1	789
    31-Aug-06	406	341	40	1	788
    But it looks like this using a VLOOKUP formula I'm using at the moment.

    Code:
    DATE   COLUMN1 COLUMN2 COLUMN3 COLUMN4 TOTAL
    01/08/06	430	466	40	1	937
    02/08/06	430	466	40	1	937
    03/08/06	430	466	40	1	937
    04/08/06	430	466	40	1	937
    05/08/06	430	466	40	1	937
    06/08/06	430	466	40	1	937
    07/08/06	430	466	40	1	937
    08/08/06	430	466	40	1	937
    09/08/06	430	466	40	1	937
    10/08/06	430	466	40	1	937
    11/08/06	430	466	40	1	937
    12/08/06	430	466	40	1	937
    13/08/06	430	466	40	1	937
    14/08/06	430	466	40	1	937
    15/08/06	430	466	40	1	937
    16/08/06	430	466	40	1	937
    17/08/06	430	466	40	1	937
    18/08/06	430	466	40	1	937
    19/08/06	430	466	40	1	937
    20/08/06	430	466	40	1	937
    21/08/06	430	466	40	1	937
    22/08/06	430	466	40	1	937
    23/08/06	430	466	40	1	937
    24/08/06	430	466	40	1	937
    25/08/06	430	466	40	1	937
    26/08/06	430	466	40	1	937
    27/08/06	430	466	40	1	937
    28/08/06	430	466	40	1	937
    29/08/06	430	466	40	1	937
    30/08/06	430	466	40	1	937
    31/08/06	430	466	40	1	937
    	13330	14446	1240	31
    As you can see it is picking up the first value for each date, which is incorrect. The idea is to create an automated solution to just paste in data to the Raw Data tab and the other tabs automatically fill.

    I use pivot tables quite a lot and sadly I cannot use a pivot table as part of this solution.

  6. #6
    Senior Member
    Join Date
    Jul 2003
    Location
    Nott'm, East Midlands
    Posts
    1,954
    Thanks
    0
    Thanked
    0 times in 0 posts
    Quote Originally Posted by Pealy View Post
    Whenever I've needed to do this I've had to add a column to each table which is a concatonation of the items I need to look-up. That's the limit of Excel's lookups.
    So you would concatinate the date and product in this instance?

  7. #7
    Member
    Join Date
    Feb 2006
    Location
    Lake District
    Posts
    69
    Thanks
    0
    Thanked
    0 times in 0 posts
    • Lakeuk's system
      • Motherboard:
      • Gigabyte P965-DS4
      • CPU:
      • Core 2 Duo E6600
      • Memory:
      • 2Gb standard spec
      • Storage:
      • 2 x 250Gb, 1 x 160Gb plus external 320Gb for backups
      • Graphics card(s):
      • fanless 7600GS
      • PSU:
      • 450watt standard came with case
      • Case:
      • Antec Sonata II
      • Monitor(s):
      • Samsung 940BF - 19" 2ms
      • Internet:
      • Demon 8Mb
    Can't be done with vlookup, found a link that indicates that INDEX/MATCH should be used but not tried it

    http://www.mvps.org/dmcritchie/excel/vlookup.htm

  8. #8
    Member
    Join Date
    Aug 2005
    Location
    Lancashire, UK
    Posts
    145
    Thanks
    0
    Thanked
    0 times in 0 posts
    Quote Originally Posted by Lead_Head View Post
    So you would concatinate the date and product in this instance?
    I'm still struggling to understand exactly what you're trying to do so I'll take a wild guess. Are you saying that you will specify a 'Type' (maybe have one results tab for each type?) and you want to see the entry for that type for each day?

    Eg. if your data is:

    TYPE DATE COLUMN1 COLUMN2 COLUMN3 COLUMN4 TOTAL
    MOBO 01-Aug-06 430 466 50 9 955
    MOBO 02-Aug-06 431 467 60 8 966
    CPU 01-Aug-06 432 468 70 7 977
    CPU 02-Aug-06 433 469 80 6 988
    PSU 02-Aug-06 434 470 90 5 999
    MOBO 03-Aug-06 435 471 10 4 920

    They you want to see different sets of data. Eg on Tab 1 (for MOBO) you just see:

    MOBO 01-Aug-06 430 466 50 9 955
    MOBO 02-Aug-06 431 467 60 8 966
    MOBO 03-Aug-06 435 471 10 4 920

    But on Tab2 (for CPU) you want to see

    CPU 01-Aug-06 432 468 70 7 977
    CPU 02-Aug-06 433 469 80 6 988
    CPU 03-Aug-06 0 0 0 0 0

    And on Tab3 (PSU)

    PSU 01-Aug-06 0 0 0 0 0
    PSU 02-Aug-06 434 470 90 5 999
    PSU 03-Aug-06 0 0 0 0 0

    I've knocked up a simple spreadsheet which does the abobve but I may have the wrong end of the stick and LakeUK could be right!

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Showing formula in Calc (Open Office)
    By XA04 in forum Help! Quick Relief From Tech Headaches
    Replies: 3
    Last Post: 22-09-2006, 09:32 PM
  2. Excel windows
    By Steve in forum Software
    Replies: 4
    Last Post: 25-07-2006, 12:44 PM
  3. Replies: 5
    Last Post: 20-07-2006, 09:05 AM
  4. Replies: 6
    Last Post: 12-08-2005, 08:50 AM
  5. Replies: 1
    Last Post: 16-06-2005, 07:35 PM

Posting Permissions

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