Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. | | | |
#5
|
|||
|
|||
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. | | | |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |