|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 9th, 2010, 05:10 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup changing data range in external workbook
I am using Excel 2007. I need to match an alpha numeric value from one
workbook with the same alpha numeric in a different workbook and have the formula return the data in the cell adjacent (right side) to the matched cell. The data in the second workbook will be in a different row each time I run it, but the column will be the same. The alpha numeric character to look up is in Column D. The data I want to show up is in Column E. Here is an example: Data to match: "43005.60 Total" I want to go to the second workbook, look for "43005.60 Total" (Column D) and have it give me the $$$ that are shown in Column E. To make matters more complicated, the files are on different network drives and the formulas need to be dummy proof for multiple beginning Excel users. I am hoping one of the brilliant forum members here can help me out. You all seem to know everything about Excel! 
|
|
#2
March 9th, 2010, 05:22 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup changing data range in external workbook
Have a look in help at VLOOKUP
See also http://spreadsheets.about.com/od/exc...320vlookup.htm http://www.timeatlas.com/5_Minute_Ti...OOKUP_in_Excel Then come back if more help is needed best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RockCounter" wrote in message ... I am using Excel 2007. I need to match an alpha numeric value from one workbook with the same alpha numeric in a different workbook and have the formula return the data in the cell adjacent (right side) to the matched cell. The data in the second workbook will be in a different row each time I run it, but the column will be the same. The alpha numeric character to look up is in Column D. The data I want to show up is in Column E. Here is an example: Data to match: "43005.60 Total" I want to go to the second workbook, look for "43005.60 Total" (Column D) and have it give me the $$$ that are shown in Column E. To make matters more complicated, the files are on different network drives and the formulas need to be dummy proof for multiple beginning Excel users. I am hoping one of the brilliant forum members here can help me out. You all seem to know everything about Excel!
|
|
#3
March 9th, 2010, 06:20 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup changing data range in external workbook
Thanks for the links. I've tried Vlookup, lookup, index/match. They all
return "#N/A." Here's the formula I'm trying. Maybe you can see something amiss in the formula? =VLOOKUP(E10,'[01-10 Matthews Recycling Plant MOCR.xls]Tally'!$A$1:$M$1000,FALSE) Where E10 is the alpha numeric, and $A$!:$m$1000 is the range to be searched on the external workbook. "Bernard Liengme" wrote: Have a look in help at VLOOKUP See also http://spreadsheets.about.com/od/exc...320vlookup.htm http://www.timeatlas.com/5_Minute_Ti...OOKUP_in_Excel Then come back if more help is needed best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RockCounter" wrote in message ... I am using Excel 2007. I need to match an alpha numeric value from one workbook with the same alpha numeric in a different workbook and have the formula return the data in the cell adjacent (right side) to the matched cell. The data in the second workbook will be in a different row each time I run it, but the column will be the same. The alpha numeric character to look up is in Column D. The data I want to show up is in Column E. Here is an example: Data to match: "43005.60 Total" I want to go to the second workbook, look for "43005.60 Total" (Column D) and have it give me the $$$ that are shown in Column E. To make matters more complicated, the files are on different network drives and the formulas need to be dummy proof for multiple beginning Excel users. I am hoping one of the brilliant forum members here can help me out. You all seem to know everything about Excel! .
|
|
#4
March 9th, 2010, 06:21 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup changing data range in external workbook
As a follow up - does it matter that my data I'm trying to match is not in
Column A? "Bernard Liengme" wrote: Have a look in help at VLOOKUP See also http://spreadsheets.about.com/od/exc...320vlookup.htm http://www.timeatlas.com/5_Minute_Ti...OOKUP_in_Excel Then come back if more help is needed best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RockCounter" wrote in message ... I am using Excel 2007. I need to match an alpha numeric value from one workbook with the same alpha numeric in a different workbook and have the formula return the data in the cell adjacent (right side) to the matched cell. The data in the second workbook will be in a different row each time I run it, but the column will be the same. The alpha numeric character to look up is in Column D. The data I want to show up is in Column E. Here is an example: Data to match: "43005.60 Total" I want to go to the second workbook, look for "43005.60 Total" (Column D) and have it give me the $$$ that are shown in Column E. To make matters more complicated, the files are on different network drives and the formulas need to be dummy proof for multiple beginning Excel users. I am hoping one of the brilliant forum members here can help me out. You all seem to know everything about Excel! .
|
|
#5
March 9th, 2010, 06:28 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup changing data range in external workbook
Edited the formula previously posted. Still getting #n/a even though I know
there should be a match. "RockCounter" wrote: Thanks for the links. I've tried Vlookup, lookup, index/match. They all return "#N/A." Here's the formula I'm trying. Maybe you can see something amiss in the formula? =VLOOKUP(E10,'[01-10 Matthews Recycling Plant MOCR.xls]Tally'!$A$1:$M$1000,5, FALSE) Where E10 is the alpha numeric, and $A$!:$m$1000 is the range to be searched on the external workbook. "Bernard Liengme" wrote: Have a look in help at VLOOKUP See also http://spreadsheets.about.com/od/exc...320vlookup.htm http://www.timeatlas.com/5_Minute_Ti...OOKUP_in_Excel Then come back if more help is needed best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RockCounter" wrote in message ... I am using Excel 2007. I need to match an alpha numeric value from one workbook with the same alpha numeric in a different workbook and have the formula return the data in the cell adjacent (right side) to the matched cell. The data in the second workbook will be in a different row each time I run it, but the column will be the same. The alpha numeric character to look up is in Column D. The data I want to show up is in Column E. Here is an example: Data to match: "43005.60 Total" I want to go to the second workbook, look for "43005.60 Total" (Column D) and have it give me the $$$ that are shown in Column E. To make matters more complicated, the files are on different network drives and the formulas need to be dummy proof for multiple beginning Excel users. I am hoping one of the brilliant forum members here can help me out. You all seem to know everything about Excel! .
|
|
#6
March 10th, 2010, 08:16 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup changing data range in external workbook
Hi
2 problems here. The lookup value has to be in the first column of the lookup range. You have to give the offset from the found lookup, for the value you want to retrieve Try =VLOOKUP(E10,'[01-10 Matthews Recycling Plant MOCR.xls]Tally'!$D$1:$E$1000,2,FALSE) -- Regards Roger Govier RockCounter wrote: Thanks for the links. I've tried Vlookup, lookup, index/match. They all return "#N/A." Here's the formula I'm trying. Maybe you can see something amiss in the formula? =VLOOKUP(E10,'[01-10 Matthews Recycling Plant MOCR.xls]Tally'!$A$1:$M$1000,FALSE) Where E10 is the alpha numeric, and $A$!:$m$1000 is the range to be searched on the external workbook. "Bernard Liengme" wrote: Have a look in help at VLOOKUP See also http://spreadsheets.about.com/od/exc...320vlookup.htm http://www.timeatlas.com/5_Minute_Ti...OOKUP_in_Excel Then come back if more help is needed best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RockCounter" wrote in message ... I am using Excel 2007. I need to match an alpha numeric value from one workbook with the same alpha numeric in a different workbook and have the formula return the data in the cell adjacent (right side) to the matched cell. The data in the second workbook will be in a different row each time I run it, but the column will be the same. The alpha numeric character to look up is in Column D. The data I want to show up is in Column E. Here is an example: Data to match: "43005.60 Total" I want to go to the second workbook, look for "43005.60 Total" (Column D) and have it give me the $$$ that are shown in Column E. To make matters more complicated, the files are on different network drives and the formulas need to be dummy proof for multiple beginning Excel users. I am hoping one of the brilliant forum members here can help me out. You all seem to know everything about Excel! .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
