If Formula's in Excel

C

CarCarq

Not really sure what to call what I want to do.

I am looking to create a 7 column spreadsheet that will pull the information
from another worksheet.

I have the initial spreadsheet (workbook 2) pulling a name from a different
workbook, (workbook 1) I then want the Name to pull all the other information
from another worksheet.

EX:
Workbook 2, Sheet 1 - Pulls name from Workbook 1, sheet 1 with a formula
that puts it in Workbook 2, Sheet 1, Column 4.

I then have an address list in Workbook 2, Sheet 2 (5 Columns in total).
What I would like to happen is the name from Workbook 2, Sheet 1, Column 4,
pulls all the address information (4 columns of information in total) to the
master list on Workbook 2, Sheet 1.

Can you please help me! Thanks so much.

Cheers,
Carla
 
P

Peo Sjoblom

Use VLOOKUP, assume the names start in D2 (column 4)
and you want the address info in columns E to H in row 2

in E2 put

=IF(ISNUMBER(MATCH($D2,[Book2]Sheet2!$A$2:$A$200,0)),VLOOKUP($D2,[Book2]Sheet2!$A$2:$E$200,COLUMN(B:B),0),"not found")

copy across 4 columns and copy down in one fell swoop


Regards,

Peo Sjoblom
 
C

CarCarq

Ok ... I think u lost me ... sorry. Here is what I need to do in simple terms:

If A6:A5000(sheet 2) =D6(sheet 1) then E6(sheet 1) should = b6:5000(sheet 2)

How do I write that in excel lingo ... thank you for your help!

Peo Sjoblom said:
Use VLOOKUP, assume the names start in D2 (column 4)
and you want the address info in columns E to H in row 2

in E2 put

=IF(ISNUMBER(MATCH($D2,[Book2]Sheet2!$A$2:$A$200,0)),VLOOKUP($D2,[Book2]Sheet2!$A$2:$E$200,COLUMN(B:B),0),"not found")

copy across 4 columns and copy down in one fell swoop


Regards,

Peo Sjoblom

CarCarq said:
Not really sure what to call what I want to do.

I am looking to create a 7 column spreadsheet that will pull the information
from another worksheet.

I have the initial spreadsheet (workbook 2) pulling a name from a different
workbook, (workbook 1) I then want the Name to pull all the other information
from another worksheet.

EX:
Workbook 2, Sheet 1 - Pulls name from Workbook 1, sheet 1 with a formula
that puts it in Workbook 2, Sheet 1, Column 4.

I then have an address list in Workbook 2, Sheet 2 (5 Columns in total).
What I would like to happen is the name from Workbook 2, Sheet 1, Column 4,
pulls all the address information (4 columns of information in total) to the
master list on Workbook 2, Sheet 1.

Can you please help me! Thanks so much.

Cheers,
Carla
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi
you may have a look at the explanation for VLOOKUP in Excel's help or
try:
http://www.contextures.com/xlFunctions02.html

in you case adapt Peo's formula to
E6: =VLOOKUP(D6,'sheet2'!$A$6:$B$5000,2,0)

or with error checking
=IF(ISNA(VLOOKUP(D6,'sheet2'!$A$6:$B$5000,2,0)),0,VLOOKUP(D6,'sheet2'!$
A$6:$B$5000,2,0))

--
Regards
Frank Kabel
Frankfurt, Germany

CarCarq said:
Ok ... I think u lost me ... sorry. Here is what I need to do in simple terms:

If A6:A5000(sheet 2) =D6(sheet 1) then E6(sheet 1) should = b6:5000(sheet 2)

How do I write that in excel lingo ... thank you for your help!
Click to expand...
=IF(ISNUMBER(MATCH($D2,[Book2]Sheet2!$A$2:$A$200,0)),VLOOKUP($D2,[Book2
]Sheet2!$A$2:$E$200,COLUMN(B:B),0),"not found")
 
Top