# 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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### acw

##### MrExcel MVP
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

#### Sethrow

##### New Member
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!

#### acw

##### MrExcel MVP
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

#### Brian from Maui

##### MrExcel MVP
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
11
Views
555
Replies
9
Views
556
Replies
38
Views
1K
Replies
3
Views
302
Replies
3
Views
526

1,171,700
Messages
5,877,014
Members
433,228
Latest member
Sarah1989

### 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