Excel Look up and Return Data

Hello all,

I have a "data table" in one Excel file (call it DATA, the table is 
sorted on a field called 'CODE') and a second Excel file (call it 
FORMULA).  I need to lookup information from the DATA table and return 
it to the FORMULA file, this will be done by matching a Raw Material 
Code number from the FORMULA file to the DATA table.  When the code is 
matched, I would like to return 5 consecutive cells (in 5 consecutive 
columns) of data to the FORMULA file.  Currently, I am using 5 vLookup 
functions to do this in the 5 consecutive cells.  I am doing this to try 
to speed up the process.

I think this can be done, but I have never used arrays of data or
functions.


Data Table

CODE	Size	Cost1	Cost2	Weight	Color
					
100	23	2	13	50	Blue
200	45	5	88	60	Green
300	67	8	74	70	Yellow
400	88	9	99	80	Black
500	98	11	66	90	White



Formula File

CODE	Size	Cost1	Cost2	Weight	Color

Thanks for any help.

Regards,

-- Alton
0
awilson4 (4)
3/22/2008 1:28:11 PM
excel 39879 articles. 2 followers. Follow

2 Replies
236 Views

Similar Articles

[PageSpeed] 54

Look like a task for INDEX with MATCH


      Data   Item cherry 7
      code data1    6
      apple 4    5
      apricot 5    3
      banana 3    4
      cherry 7
      damson 6
      orange 5
      peach 3
      pear 4
      plum 2

The range apple...plum is called CODE
The 4....2 is called Data1
The cell with 'cherry' is called ITEM
The formula in next cell F1 is =INDEX(data1,MATCH(Item,code,1)+ROW()-1)
Copy this down
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Alton B. Wilson" <awilson4@cinci.rr.com> wrote in message 
news:%235x%23lgBjIHA.3512@TK2MSFTNGP03.phx.gbl...
> Hello all,
>
> I have a "data table" in one Excel file (call it DATA, the table is sorted 
> on a field called 'CODE') and a second Excel file (call it FORMULA).  I 
> need to lookup information from the DATA table and return it to the 
> FORMULA file, this will be done by matching a Raw Material Code number 
> from the FORMULA file to the DATA table.  When the code is matched, I 
> would like to return 5 consecutive cells (in 5 consecutive columns) of 
> data to the FORMULA file.  Currently, I am using 5 vLookup functions to do 
> this in the 5 consecutive cells.  I am doing this to try to speed up the 
> process.
>
> I think this can be done, but I have never used arrays of data or
> functions.
>
>
> Data Table
>
> CODE Size Cost1 Cost2 Weight Color
>
> 100 23 2 13 50 Blue
> 200 45 5 88 60 Green
> 300 67 8 74 70 Yellow
> 400 88 9 99 80 Black
> 500 98 11 66 90 White
>
>
>
> Formula File
>
> CODE Size Cost1 Cost2 Weight Color
>
> Thanks for any help.
>
> Regards,
>
> -- Alton 


0
bliengme5824 (3040)
3/22/2008 1:33:25 PM
On Mar 22, 6:33=A0pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:
> Look like a task for INDEX with MATCH
>
> =A0 =A0 =A0 Data =A0 Item cherry 7
> =A0 =A0 =A0 code data1 =A0 =A06
> =A0 =A0 =A0 apple 4 =A0 =A05
> =A0 =A0 =A0 apricot 5 =A0 =A03
> =A0 =A0 =A0 banana 3 =A0 =A04
> =A0 =A0 =A0 cherry 7
> =A0 =A0 =A0 damson 6
> =A0 =A0 =A0 orange 5
> =A0 =A0 =A0 peach 3
> =A0 =A0 =A0 pear 4
> =A0 =A0 =A0 plum 2
>
> The range apple...plum is called CODE
> The 4....2 is called Data1
> The cell with 'cherry' is called ITEM
> The formula in next cell F1 is =3DINDEX(data1,MATCH(Item,code,1)+ROW()-1)
> Copy this down
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVPhttp://people.stfx.ca/bliengme
> remove caps from email
>
> "Alton B. Wilson" <awils...@cinci.rr.com> wrote in messagenews:%235x%23lgB=
jIHA.3512@TK2MSFTNGP03.phx.gbl...
>
>
>
> > Hello all,
>
> > I have a "data table" in one Excel file (call it DATA, the table is sort=
ed
> > on a field called 'CODE') and a second Excel file (call it FORMULA). =A0=
I
> > need to lookup information from the DATA table and return it to the
> > FORMULA file, this will be done by matching a Raw Material Code number
> > from the FORMULA file to the DATA table. =A0When the code is matched, I
> > would like to return 5 consecutive cells (in 5 consecutive columns) of
> > data to the FORMULA file. =A0Currently, I am using 5 vLookup functions t=
o do
> > this in the 5 consecutive cells. =A0I am doing this to try to speed up t=
he
> > process.
>
> > I think this can be done, but I have never used arrays of data or
> > functions.
>
> > Data Table
>
> > CODE Size Cost1 Cost2 Weight Color
>
> > 100 23 2 13 50 Blue
> > 200 45 5 88 60 Green
> > 300 67 8 74 70 Yellow
> > 400 88 9 99 80 Black
> > 500 98 11 66 90 White
>
> > Formula File
>
> > CODE Size Cost1 Cost2 Weight Color
>
> > Thanks for any help.
>
> > Regards,
>
> > -- Alton- Hide quoted text -
>
> - Show quoted text -

i think this might help u, try out if i am wrong guide me

 Dim va1 As String
Application.ScreenUpdating =3D False

Sheets("Formula Table").Select
Range("a2").Select
    Selection.Copy
     va1 =3D ActiveCell.Value
    va1 =3D Range("a2")
    ActiveSheet.Previous.Select
    Range("A:A").Select
        Cells.Find(what:=3Dva1, after:=3DActiveCell, LookIn:=3DxlFormulas,
lookat:=3D _
        xlPart, searchorder:=3DxlByRows, SearchDirection:=3DxlNext,
MatchCase:=3DFalse _
        , searchformat:=3DFalse).Activate
        ActiveCell.Offset(0, 0).Select
        ActiveCell.FormulaR1C1 =3D ActiveCell.Value
       Range(Selection, Selection.End(xlToRight)).Select


      Application.CutCopyMode =3D False
    Selection.Copy
    ActiveSheet.Next.Select
    Range("a2").Select
    ActiveSheet.Paste
    Range("A2").Select
    Application.CutCopyMode =3D False
     Application.ScreenUpdating =3D True

     Exit Sub


  End Sub
0
muddanmadhu (119)
3/23/2008 12:20:35 PM
Reply:

Similar Artilces:

exit excel within vba
Hello All, I have a vba code, at the end of this code I want to exit excel not close. If I close the excel workbook I can not run excel again from the scheduler. Can anyone provide me help?? thanx all. Murat Demir Hekimoglu You want to quit excel? Application.quit could be what you're looking for. Murat Demir HEKIMOGLU wrote: > > Hello All, > > I have a vba code, at the end of this code I want to exit excel not close. > > If I close the excel workbook I can not run excel again from the scheduler. > > Can anyone provide me help?? > > thanx all....

Excel Bar Graph Questions
I want to compare FY08 & FY09 data (By each mth) using excel bar graph. So, on the X-axis need to show 08 & 09 bar graph in DIFFERENT color. What's the restrictions now I can see is ONLY ONE color shown on data on X axis. Anyone knows how to do it? Many Thanks Hi, Did you move one of the series to the secondary axis? If so see http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Ruby" <Ruby@discussions.microsoft.com> wrote in message news:223C6B4E-345D-40B7-A217-AEA3FA686F...

Data and Other Records
Hi - I'm building a pivot table consisting of counts of a list of items. I want to include only the first 50 items in detail, but lump all the others into an "other" category with a count on it, instead of detail on records above 50. Hopefully this makes some sense! Thanks, Greg. -- groggrog ------------------------------------------------------------------------ groggrog's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27400 View this thread: http://www.excelforum.com/showthread.php?threadid=469155 ...

Excel text box #2
Is there a way to set the default text alignment to "center" for both vertical and horizontal in the draw text box? I am using Excel 2000. I found it for the cell default. That's what I want to do with the draw text box. -- Thanks To get the horizontal centering--just select your text and hit the Center Icon on the format toolbar--just like you would to center text in a cell. I had to hit enter a few times (at the beginning of the text) to center it vertically. rvrl wrote: > > Is there a way to set the default text alignment to "center" for both > ...

Can Excel 2007 link with Outlook 2007
I have a list of companies names that I need to add the addresses to. I don't want them to be on the same worksheet. I will need to add compaines to the list from time to time. Also Is it possible to link these addresses with outlook? Thanks, Dan ...

Making data span 2 columns
Hi, I'm new to this community and new to the RMS system. I am trying to modify the PrintTransactionDetails sub in the receipt.xml to be 2 columns and put the entry.description information onto another row. The person who writes the descriptions likes to be wordy and I need the entire 40 columns in order to hold the text. I changed the 3 columns into 2 with a format of 71% for the item number or description and 29% for the sales amount. The description and a null field occupy the first row and the item number and amount occupies the second row. I have the transaction d...

Excel missing file
I am a registered user of Office XP Pro. Excel will not start. It is missing a file. In the process of moving, disks in storage. Where can I go online to download the missing file? And, how? ...

How can i change chart data point format based on value?
I have a simple column chart with a single data series. I would like to change the color of an individual data point column depending on value. Thanks very much. You want to make a conditional chart: http://peltiertech.com/Excel/Charts/ConditionalChart1.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Harold96" <Harold96@discussions.microsoft.com> wrote in message news:3674450D-70A9-4227-B29A-36262B7BE723@microsoft.com... >I have a simple column chart with a single data series. I would...

Excel integration of models in various workbooks
I have a few different models in seperate excel workbooks ... one for cars, one for houses, one for retirement accounts.. I recently had the idea to take outputs from each of these models and plug them into a single workbook something like life.xls. What I'm wondering is if there is a way to wrap them (the house and car models) with an interface (VBA?) which I can pass parameters and can call from a macro in life.xls to retrieve the output from. Essentially I'd like each of the workbooks (house,..cars) to be like a callable function into which I can pass parameters, and which return...

GDI Problem with Excel and two colour fill effect charting
Over the last few months I have had the same problem in excel with all charts where I've used a two colour fill effect for either a data series or background. When I use this feature the entire chart is only viewable in black and white and excel takes a long time to actually draw the chart. However if I print the chart or even copy and paste into powerpoint all the colours are as they should be. The mpeg moviw below shows how excel draws an example chart when the chart sheet is activated http://www.paperlessbooks.co.uk/barchart_example.avi and to see how excel prints to pdf see the pdf...

Looking at a message without opening it
Hi there, This is an Outlook question, despite me mentioning Outlook Express below.. In Outlook Express, I can look at a message in raw format (including ALL email headers, mime parts, etc) by right-clicking on the message in the inbox, select "Properties", select "Details" tab, select "Message Source" button. This will open a text window displaying the whole email in raw format. Can I do that with Outlook as well? Right-click, "Options" does not give me the same possibilities like in Outlook Express. But perhaps there is a plugin or makro I cou...

Looking for a consultant Expert on Exchange server
We are looking for a consultant who has extensive knowledge on Exchange 2003 and DNS Server configuratin can visit our loaction in here in DC. thanks Sunu On Mon, 23 Oct 2006 13:26:02 -0700, Sunu <Sunu@discussions.microsoft.com> wrote: >We are looking for a consultant who has extensive knowledge on Exchange 2003 >and DNS Server configuratin can visit our loaction in here in DC. > >thanks >Sunu You pay my air fare from London and put me up in the Hilton and I'm there, otherwise you would probably want to contact somewhere slightly more local! DC area, hit me offl...

geographic map data
I would like to find a simple set of geographic map data (Latitude and Longitude) in EXCEL tables for drawing country outline maps for use in EXCEL Charts ...

Active X Controls Shrinking on Excel worksheet
I have a workbook and sometimes the ActiveX controls such as listbox, options, checkboxes on the worksheet shrinks or expands while clicking on the controls. Does somebody has experirenced same kind of problem or somebody knows the fix. The only to fix this problem is to restart the machine again. Thanks in advance. ...

Calculate total number off cells with data in
Hi All Hope I find you well I have a spreadsheet that has a number of cells that may or may not have data in them depending on curtain criteria. What I would like to be able to do is count the number of cells with data in (data is alphanumerical) and display the total number of cells with data in in a different cell. Any ideas? Hi Gazza, > What I would like to be able to do is count the number of cells with data in (data is alphanumerical) and display the total number of cells with data in in a different cell. Any ideas? > Check out the COUNTA worksheet function. Regards, Jan Kare...

excel #13
I need to know if there is a way to put a scroll bar with text, as a pull down menu in a cell. Hi Chelo, Check out Data | Validation | List. HTH -- Regards, Zack Barresse, aka firefytr "Chelo" <Chelo@discussions.microsoft.com> wrote in message news:8BB85A00-4175-47C7-A4EF-52260FD5049A@microsoft.com... >I need to know if there is a way to put a scroll bar with text, as a pull > down menu in a cell. ...

Please help me with my Excel
I have got a new lap top and I have a good antivirus and spyware. my pc works good but once I open Excel my CPU jumps up to %100 and the strange thing is that when I click on other programs my CPU comes back to %4. The more strange thing is that when for example I press F2 to edit a cell , CPU works good but when I press Enter, again it goes back to %100. Here our IT man reinstalled my MS-Office and he did some other exotic operations but there was not any slightest change and the problem is still existing and going on my nerve. Please scientists help me ! I work with Excel 7 hrs per day ...

non linear regression in excel
Hello I have some charts which I am trying to fit lines of best fits to. The general shape of these curves is something representing '4 o' clock' on a clock. I am trying to do non-linear regression to get the closest fit to these curves but the trendline functions on excel are very limited (ie logarthmic,exponential,polynomial etc) and do not produce a sufficient trendline. I was wondering if anyone knows how I could possibly produce the trendline that I require that fits the data on the curve. Many thanks for your help. Warm regards Nikeel - If the general shape is a singl...

Look up Address problems!
Hi I wonder if anyone can help with this problem. I have imported a number of Accounts into our CRM system. All is working fine except that I cannot link an invoice or delivery address to an order or quote using the lookup address! I imported the accounts originally using the administrator account. If I logon to the CRM as myself and then create an order/quote (that has the orignal account owner as the administrator) and use the look up address, this works fine! I get the primary account address returned. If I then create an account as myself and then try to create an order/quote and use ...

Determine if data is trending UP or DOWN
All I posted this question in another area with little activity. Please review my question and reply with any suggestions http://groups.google.com/group/microsoft.public.excel.worksheetfunctinos/browse_thread/thread/51433fe1f0242504/f908dfbcb5ae9487?hl=en#f908dfbcb5ae9487 Ignore all points except the first and the last. If the last point is greater than the first point, the trend is up. -- Gary''s Student - gsnu200719 "Steve" wrote: > All I posted this question in another area with little activity. > Please review my question and reply with any suggestions > ...

Excel 2002: Can I save all files by just one click ?
Hi, I have 10 working files in an active window. There is no Save All button under the file menu. May I know if I can save all the files at one click without having to exit excel ? Thanks Low This delivers it in 2 clicks ... Hold down Shift key, click File > Close All Then click "Yes to All" in the ensuing prompt to save changes Success? Celebrate it, hit the YES below -- Max Singapore --- "Mr. Low" wrote: > I have 10 working files in an active window. > There is no Save All button under the file menu. > May I know if I c...

V Look up not yielding all results needed
I am using a validation table in column b and based off my selection I used the following formula to populate the pricing for the item. =IF(ISNA(VLOOKUP(B14:B39,'look up'!C4:H159,2,FALSE)),"",VLOOKUP(B14:B39,'look up'!C4:H159,2,FALSE)) Everything works smooth unless I try to select data which does not fall alphabetically, problem is the users I am creating this for will not neccassarily be selecting their products alphabetically. EX: If I select Adhesive Vinyl - Discount Backlit Vinyl - Standard and then try to select Adhesive Vinyl - Standard it will not pro...

Dynamic Excel
Dear All Is there anything that I can do in Excel to my Excel report dynamic? For eg... is it possible to create something which will create results based on figures keyed in... ? Any suggestions would be welcomed... It would be good if u could send me similar Excel project of yours... to me as reference... Cheers --- Message posted from http://www.ExcelForum.com/ Hi this is a little bit vague :-) maybe as a starting point: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/exce...

How do I remove the (blank) from an excel pivot table?
I am trying to create a pivot table from a liston an excel spreadsheet. Many of the items on the list will be blank. On the pivot table, the blank items show up as (blank) - I want the cell to be empty! I tried setting the table empty values but that doesn't work...any help would be greatly apprecated... If you click the small black triangle near the top of the Table, a menu will appear. Just clear the checkbox next to (blank) -- Gary''s Student "MarkfromAZ" wrote: > I am trying to create a pivot table from a liston an excel spreadsheet. Many > of the...

Refreshing a Microsoft Office Excel Chart Object
Can a Microsoft Office Excel Chart Object be refreshed by linking it to an excel spreadsheet? The "chart object" is being copied from the originating excel spreadsheet, using the special past function to make an Excel Chart Object in a Word doc. DonC, Use Paste Special, pasting it as an Excel Chart Object in the Word document. Use the Paste Link button. Now it's linked to the cells of the original worksheet. -- Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "DonC" <don.cook@farmersinsurance.com> w...