Formula please
 

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.

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" wrote in message ...
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.

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

"Steved" wrote in message ...
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.

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

"Steved" wrote in message
...
| 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
|
| "Steved" wrote in message
...
| 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.
|
|
|

Thankyou Scott

"Scott Orchard" wrote:

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

"Steved" wrote in message
...
| 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
|
| "Steved" wrote in message
...
| 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.
|
|
|

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" wrote in message ...
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

"Steved" wrote in message ...
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.

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.

Thanks David Both of you have been off great assistance.

Thankyou.

"David McRitchie" wrote:

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.