Formula please

S

Steved

Hello from Steved

I have a file named Bus.xls
In it I have Values in Col A, B, C ,D

In a file Named Reports.xls I have information in Col A

ok in Col A in Bus.xls I have a value "B0101"
ok in Col A in Reports.xls I have a value "B0101"
What Formula would I use to lookup B0101 in Bus.xls to put
the information in Col B,C,D. of reports.xls Please.

Thankyou.
 
S

Steved

Hello from Steved

My attempt is below is their a index and match I can use to do the same.

=IF($A$1="","",VLOOKUP(A:A,BusType!A:D,2,0))
=IF($A$1="","",VLOOKUP(A:A,BusType!A:D,3,0))
=IF($A$1="","",VLOOKUP(A:A,BusType!A:D,4,0))

David McRitchie said:
Take a look at VLOOKUP Worksheet Function in HELP, and in
http://www.mvps.org/dmcritchie/excel/vlookup.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Steved said:
Hello from Steved

I have a file named Bus.xls
In it I have Values in Col A, B, C ,D

In a file Named Reports.xls I have information in Col A

ok in Col A in Bus.xls I have a value "B0101"
ok in Col A in Reports.xls I have a value "B0101"
What Formula would I use to lookup B0101 in Bus.xls to put
the information in Col B,C,D. of reports.xls Please.

Thankyou.
Click to expand...
Click to expand...
 
S

Scott Orchard

Try putting this formula into cell B1 of reports.xls ...

=INDEX([bus.xls]SHEET!B:B,MATCH($A1,[bus.xls]SHEET!$A:$A,0))

For columns C & D, you can just copy the formula across.
You can then copy down as many rows as you need.
Rgds,
Scott

| Hello from Steved
|
| My attempt is below is their a index and match I can use to do the same.
|
| =IF($A$1="","",VLOOKUP(A:A,BusType!A:D,2,0))
| =IF($A$1="","",VLOOKUP(A:A,BusType!A:D,3,0))
| =IF($A$1="","",VLOOKUP(A:A,BusType!A:D,4,0))
|
| "David McRitchie" wrote:
|
| > Take a look at VLOOKUP Worksheet Function in HELP, and in
| > http://www.mvps.org/dmcritchie/excel/vlookup.htm
| > ---
| > HTH,
| > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
| > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
| > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
| >
| > > Hello from Steved
| > >
| > > I have a file named Bus.xls
| > > In it I have Values in Col A, B, C ,D
| > >
| > > In a file Named Reports.xls I have information in Col A
| > >
| > > ok in Col A in Bus.xls I have a value "B0101"
| > > ok in Col A in Reports.xls I have a value "B0101"
| > > What Formula would I use to lookup B0101 in Bus.xls to put
| > > the information in Col B,C,D. of reports.xls Please.
| > >
| > > Thankyou.
| >
| >
| >
 
D

David McRitchie

Hi Steve,
Close but you have a few changes to make:

For Column B
B1: =VLOOKUP($A1,BusType!$A$1:$F$200,COLUMN(B1),0)
B2: =VLOOKUP($A2,BusType!$A$1:$F$200,COLUMN(B2),0)
B3: =VLOOKUP($A3,BusType!$A$1:$F$200,COLUMN(B3),0)
B4: =VLOOKUP($A4,BusType!$A$1:$F$200,COLUMN(B4),0)
B5: =VLOOKUP($A5,BusType!$A$1:$F$200,COLUMN(B5),0)
B6: =VLOOKUP($A6,BusType!$A$1:$F$200,COLUMN(B6),0)

For Column C
C1: =VLOOKUP($A1,BusType!$A$1:$F$200,COLUMN(C1),0)
C2: =VLOOKUP($A2,BusType!$A$1:$F$200,COLUMN(C2),0)
C3: =VLOOKUP($A3,BusType!$A$1:$F$200,COLUMN(C3),0)
C4: =VLOOKUP($A4,BusType!$A$1:$F$200,COLUMN(C4),0)
C5: =VLOOKUP($A5,BusType!$A$1:$F$200,COLUMN(C5),0)

All you need is the formula in B1 and use the fill handle
to generate the others
http://www.mvps.org/dmcritchie/excel/fillhand.htm

You can use a defined name table instead of having the
range specified in the formula, look bak at
http://www.mvps.org/dmcritchie/excel/vlookup.htm

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Steved said:
Hello from Steved

My attempt is below is their a index and match I can use to do the same.

=IF($A$1="","",VLOOKUP(A:A,BusType!A:D,2,0))
=IF($A$1="","",VLOOKUP(A:A,BusType!A:D,3,0))
=IF($A$1="","",VLOOKUP(A:A,BusType!A:D,4,0))

David McRitchie said:
Take a look at VLOOKUP Worksheet Function in HELP, and in
http://www.mvps.org/dmcritchie/excel/vlookup.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Steved said:
Hello from Steved

I have a file named Bus.xls
In it I have Values in Col A, B, C ,D

In a file Named Reports.xls I have information in Col A

ok in Col A in Bus.xls I have a value "B0101"
ok in Col A in Reports.xls I have a value "B0101"
What Formula would I use to lookup B0101 in Bus.xls to put
the information in Col B,C,D. of reports.xls Please.

Thankyou.
Click to expand...
Click to expand...
Click to expand...
 
D

David McRitchie

I see you already got a working solution from Scott, I didn't look back at your
original question where it appears that your are referring to
workbooks as if they are worksheets.
 
Top