How to get specific cell data from one sheet!

[Paul]

HI I on sheet 2 of an excel file I have
Name Age weight location
paul 10 150 calif
joe 8 90 az


on sheet 2 I have
paul
joe

need on sheet 2

paul 150 calif

joe 50 az

and would like to get the wieght and location for each and place them
in cells within the same row as the corresponding name.

and on sheet 1 I want to pull corresponding information.
On sheet one I will only have the name and on sheet 2
the names can be located in different locations so just a =sheet2,C2
will not work because the data location is not static. I do not want to
have to change the formula every time there is new data. Just wondering if
there is a way that excel can list
desired attributes on sheet on by just providing the name?
Thanks

 

[Gord Dibben]

You want programming or will worksheet functions do?

In sheet1 enter the names down column A in any order.

In B1 enter =VLOOKUP(A1,Sheet2!$A$2:$D$20,2,FALSE)

In C1 enter =VLOOKUP(A1,Sheet2!$A$2:$D$20,2,FALSE)

Copy these down as far as you have names in column A..........I used 20 rows.

Or use a Data Validation List dropdown in A2 with your list of names to pick
from.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Typo alert!

In C1 enter =VLOOKUP(A1,Sheet2!$A$2:$D$20,2,FALSE)
should be:
In C1 enter =VLOOKUP(A1,Sheet2!$A$2:$D$20,4,FALSE)

(copy|paste is dangerous <vbg>)

And to the original poster...

Debra Dalgleish has lots of notes on =vlookup():
http://www.contextures.com/xlFunctions02.html

 

[Paul]

Hi thanks for the response. Do you know if this could be done in a macro as
well!

 

[Gord Dibben]

Thanks Dave.

Gord

Typo alert!

In C1 enter =VLOOKUP(A1,Sheet2!$A$2:$D$20,2,FALSE)
should be:
In C1 enter =VLOOKUP(A1,Sheet2!$A$2:$D$20,4,FALSE)

(copy|paste is dangerous <vbg>)

And to the original poster...

Debra Dalgleish has lots of notes on =vlookup():
http://www.contextures.com/xlFunctions02.html

 

[Paul]

Hi, not sure I understand the formula but on sheet 1 in cell D4 I have the
name as (lastname,firstname,middleinitial) that I want to search on sheet 2
but the name is listed as first name,lastname,middle initial. On the second
sheet I have columns as follows
A B C D E
lastname firstname position salary boss

on sheet 1 I need the boss so I tried
VLOOKUP(D4,SHEET2!E2,1,FALSE) which creates an error.
I would like to search through 20 rows in column E
I think the error may be because of the way the total name
(lastname,firstname,middleinit) is combined into 1 cell on sheet 1
and they first and last names are listed in seperate cells on sheet2.
Thanks.

 

[Gord Dibben]

Paul

For VLOOKUP you need a lookup value, a lookup array and a column within that
array.

First of all I would break the sheet1(lastname, firstname,middle initial)cell
into into 3 columns using Data>Text to Columns so you can look for the actual
value in a cell.

I don't understand what you want returned and where on sheet1 from sheet2

Values from sheet2 returned to which cells on sheet1?

Your original post had different data and layout.

Changing examples in midstream is too confusing this old guy.

You could send me your workbook if you want.

Change the AT and DOT in my email address to send to me.


Gord

 

[Paul]

Hi thanks for the response. I first tried to provide a simple example but I
think it just led to more confusion. If I could send you the workbook that
would be great, did not see your email though.
thanks.

 

[Gord Dibben]

gorddibbATshawDotca

Make the changes.


Gord

 

[Gord Dibben]

Paul

If you're still following this thread please note I received your email and
saved the vlookup.txt file but<sheepish grin> managed to lose the the email with
description of what you want for results and your return address..

Please re-send.

Thanks, Gord