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 :)

I think a PivotTable would solve your problem. Have you used the technique before?

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?

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.

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.

Quote:

Originally Posted by Pealy

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?

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

Quote:

Originally Posted by Lead_Head

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!