# vlookup? what adam did on the 11/11/05?!

#### Sethrow

##### New Member
Hi this is my first post I've had a look round and wow I never knew you could do so much with excel! I'm definately gonna be back I'm considered good at excel at my company but if I could learn 10 of the stuff on here I would be a legend to them all lol

Anyway I can't find an answer to this problem anywhere I'm sure its simple but its got me stumped!

I've got a table structured like this (sorry I will learn proper way to post tables soon but it is late):
A----------B----------C
John----10/11/05---67
John----12/11/05---4
John----13/11/05---3
Jack----10/11/05---9
Jack----11/11/05---1
Jack----12/11/05---2
Jack----13/11/05---45

What I want is to have a sheet for each of Adam, John and Jack that shows there data. I have cell A1 that holds there name and the A3 onwards has the date and I want B3 onwards to hold the numbers from column C above that relate to the correct day and person.

This is a simplified version of what I want but if I can get this working I think I can get what I want to work.

Sorry if this is really simple but please post an answer it would really help me!

Oh by the way the dates are not always continous as you can see from john if he didnt sell anything one day then nothing appears. Also the sheets have all days down the left hand side and all sheets show the same dates ie 01/11/05-30/11/05.

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi

In B3 enter the formula
Code:
``=SUMPRODUCT(--(Sheet1!\$A\$1:\$A\$11=\$A\$1),--(Sheet1!\$B\$1:\$B\$11=A3),(Sheet1!\$C\$1:\$C\$11))``

Adjust the ranges in Sheet1 to suit. Copy down as required.

HTH

Tony

That is awesome little tweaking and it works a treat! Out of interest what does the -- do? Think I understand how it works otherwise

Thank you you've really helped me out!

Hi

The -- converts the boolean value of True / False to a number. The first - will make it negative, the second will convert it back to positive. True will come out as 1 and False as 0.

Tony

Sethrow said:
Hi this is my first post I've had a look round and wow I never knew you could do so much with excel! I'm definately gonna be back I'm considered good at excel at my company but if I could learn 10 of the stuff on here I would be a legend to them all lol

Anyway I can't find an answer to this problem anywhere I'm sure its simple but its got me stumped!

I've got a table structured like this (sorry I will learn proper way to post tables soon but it is late):
A----------B----------C
John----10/11/05---67
John----12/11/05---4
John----13/11/05---3
Jack----10/11/05---9
Jack----11/11/05---1
Jack----12/11/05---2
Jack----13/11/05---45

What I want is to have a sheet for each of Adam, John and Jack that shows there data. I have cell A1 that holds there name and the A3 onwards has the date and I want B3 onwards to hold the numbers from column C above that relate to the correct day and person.

This is a simplified version of what I want but if I can get this working I think I can get what I want to work.

Sorry if this is really simple but please post an answer it would really help me!

Oh by the way the dates are not always continous as you can see from john if he didnt sell anything one day then nothing appears. Also the sheets have all days down the left hand side and all sheets show the same dates ie 01/11/05-30/11/05.

See,

http://www.mrexcel.com/board2/viewtopic.php?t=179640&start=10

Replies
5
Views
304
Replies
5
Views
292
Replies
4
Views
472
Replies
2
Views
463
Replies
4
Views
366

1,211,435
Messages
6,101,853
Members
447,758
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back