quick excel problem

[Joe_Fox]

Hi,

I have two worksheets:

First one contains a long list of names with office
Second one contains a shorter list of names only

All names in the second worksheet are present in the first worksheet.

What I need to do is pull the office from the first sheet into the second sheet alongside the relevant name:

For example:

sheet1

[b]name   Office[/b]
Joe    Leeds
Bob    Liverpool
Carol  Manchester

sheet 2

[B]name[/B]
Joe
Bob

I need the result to be

[B]name  Office[/B]
Joe   Leeds
Bob   Liverpool

Any ideas?

 

[bocadillo]

That's bollocks. Everybody knows that I don't work at the Liverpool office.

 

[Matt_B]

use a vlookup

=VLOOKUP($A1,Sheet1!$A$1:$B$3,2,FALSE)

Assuming columns are A and B

 

[spionfox]

That's bollocks. Everybody knows that I don't work.

At least it is out of choice though :icon_wink

 

[bocadillo]

At least it is out of choice though :icon_wink

Not actually true.

 

[Joe_Fox]

Cheers

 

[Darth Vodka]

use a vlookup

=VLOOKUP($A1,Sheet1!$A$1:$B$3,2,FALSE)

Assuming columns are A and B

VLOOKUPs are for amateurs

use MATCH & INDEX

oh and you don't need the FALSE if you're feeling lazy, you can just put the comma in =VLOOKUP($A1,Sheet1!$A$1:$B$3,2,)

 

[Joe_Fox]

give us an example of how you would use the MATCH & INDEX then mate.

 

[Darth Vodka]

ok, attached

the formulae produce the same results..

but a VLOOKUP means you can't alter the lookup range at all

with MATCH/INDEX you can use different sheets or even have the two columns staggered with one way below

and VLOOKUP can only go from left to right...MATCH/INDEX can go anyways

 

[Darth Vodka]

ooops

 

[Siouxsie]

ok, attached

the formulae produce the same results..

but a VLOOKUP means you can't alter the lookup range at all

with MATCH/INDEX you can use different sheets or even have the two columns staggered with one way below

and VLOOKUP can only go from left to right...MATCH/INDEX can go anyways

informative - thanks

 

[Darth Vodka]

informative - thanks

oooh a wider audience

shall i do a demo SUMPRODUCT too? :icon_bigg

 

[bocadillo]

oooh a wider audience

shall i do a demo SUMPRODUCT too? :icon_bigg

You can do sorting on a column other than the first in a few days time. And making the result of a formula act as a number and not as a formula.

 

[Siouxsie]

oooh a wider audience

shall i do a demo SUMPRODUCT too? :icon_bigg

I use excel all the time at work but since my computer studies O level dealt with punch cards, paper tapes and programming in BASIC, with an overnight link to the mainframe at County Hall, and I've never had any training in excel, my knowledge is unfortunately limited to the basics. I'm aware there are often better ways of doing things than the way I'm using and I do try to pick my younger colleagues' brains.

I'm sad enough to find this thread interesting :icon_redf

 

[Darth Vodka]

I use excel all the time at work but since my computer studies O level dealt with punch cards, paper tapes and programming in BASIC, with an overnight link to the mainframe at County Hall, and I've never had any training in excel, my knowledge is unfortunately limited to the basics. I'm aware there are often better ways of doing things than the way I'm using and I do try to pick my younger colleagues' brains.

I'm sad enough to find this thread interesting :icon_redf

mrexcel.com is good, but feel free to ask anything on here...