how to lookup value on another sheet?

I'm going to oversimply my example for the sake of making my point, so
please don't ask "why bother doing that?"

Sheet1 has a list of product ID (SKU) values.    Sheet2 has a list of *some*
of those values, plus another cell that represents the cost of that item.
How can I put the cost value from Sheet2 next to the ID value in Sheet1,
where Sheet2 has the cost.

Example of Sheet1:

ID                COST
123              {the formula I want will pull the $75 value from Sheet2
data}
124              {the formula I want wont find answer in Sheet2 data}
125              {the formula I want will pull the $20 value from Sheet2
data}
126              {the formula I want wont find answer in Sheet2 data}

Example of Sheet2:

ID                NAME                COST
123              Golf Club             $75
125              Book                    $20


So what formula can I put in Sheet1 to pull the cost data WHERE IT FINDS A
MATCH in Sheet2?   Thanks.


0
1/30/2005 9:20:56 PM
excel 39879 articles. 2 followers. Follow

2 Replies
460 Views

Similar Articles

[PageSpeed] 56

Try =vlookup()

Visit Debra Dalgleish's site:
http://www.contextures.com/xlFunctions02.html

for some nice instructions.

(BTW, this is a very common task for excel users to do.)

AFN wrote:
> 
> I'm going to oversimply my example for the sake of making my point, so
> please don't ask "why bother doing that?"
> 
> Sheet1 has a list of product ID (SKU) values.    Sheet2 has a list of *some*
> of those values, plus another cell that represents the cost of that item.
> How can I put the cost value from Sheet2 next to the ID value in Sheet1,
> where Sheet2 has the cost.
> 
> Example of Sheet1:
> 
> ID                COST
> 123              {the formula I want will pull the $75 value from Sheet2
> data}
> 124              {the formula I want wont find answer in Sheet2 data}
> 125              {the formula I want will pull the $20 value from Sheet2
> data}
> 126              {the formula I want wont find answer in Sheet2 data}
> 
> Example of Sheet2:
> 
> ID                NAME                COST
> 123              Golf Club             $75
> 125              Book                    $20
> 
> So what formula can I put in Sheet1 to pull the cost data WHERE IT FINDS A
> MATCH in Sheet2?   Thanks.

-- 

Dave Peterson
0
ec357201 (5290)
1/30/2005 9:42:20 PM
got it to work, thanks!

"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:41FD54BC.BCF178A8@netscapeXSPAM.com...
> Try =vlookup()
>
> Visit Debra Dalgleish's site:
> http://www.contextures.com/xlFunctions02.html
>
> for some nice instructions.
>
> (BTW, this is a very common task for excel users to do.)
>
> AFN wrote:
> >
> > I'm going to oversimply my example for the sake of making my point, so
> > please don't ask "why bother doing that?"
> >
> > Sheet1 has a list of product ID (SKU) values.    Sheet2 has a list of
*some*
> > of those values, plus another cell that represents the cost of that
item.
> > How can I put the cost value from Sheet2 next to the ID value in Sheet1,
> > where Sheet2 has the cost.
> >
> > Example of Sheet1:
> >
> > ID                COST
> > 123              {the formula I want will pull the $75 value from Sheet2
> > data}
> > 124              {the formula I want wont find answer in Sheet2 data}
> > 125              {the formula I want will pull the $20 value from Sheet2
> > data}
> > 126              {the formula I want wont find answer in Sheet2 data}
> >
> > Example of Sheet2:
> >
> > ID                NAME                COST
> > 123              Golf Club             $75
> > 125              Book                    $20
> >
> > So what formula can I put in Sheet1 to pull the cost data WHERE IT FINDS
A
> > MATCH in Sheet2?   Thanks.
>
> -- 
>
> Dave Peterson


0
1/30/2005 9:56:10 PM
Reply:

Similar Artilces:

Pivotable
Is there a method of hiding zero values automatically in pivot tables? Thanks. Double click the field header Uncheck "Show items with no data -- Message posted from http://www.ExcelForum.com Thanks for your response. I have followed your instructions but the zero values remain in the report. Any other ideas. >-----Original Message----- >Double click the field header >Uncheck "Show items with no data" > > >--- >Message posted from http://www.ExcelForum.com/ > >. > ...

Identifying the top five values in multiple groups
I've got a spreadsheet of pay information for about eight hundred people. Each person is on one of eight salary scales I'd like to create a new worksheet that shows the details of just the 5 highest paid people in each scale (name, dept, salary, etc.) - and also the five lowest. Ideally, I'd like also to be able to vary that number - eg the top ten, the highest, etc.. Can someone help? Thanks Suppose you have data in Sheet1 in the below format Col A Col B Col C Name Scale Salary a1 1 101 a2 1 102 a3 1 103 a4 2 104 In Sheet2 cel...

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

File handle in global value
I open a file in one thread, save the file handle in a global value(ignoring for a moment that its not recommended), then close the thread , open a new thread and use the handle inside it to access the file and finally close the file in this thread. Is it legal from the point of view of C++/MFC(again,ignoring for a moment that its not recommended)? Regards Mandi >I open a file in one thread, save the file handle in a global value(ignoring >for a moment that its not recommended), then close the thread , open a new >thread and use the handle inside it to access the file and f...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

Problem with Null value elimintation criteria
Access 2007 on Vista. I'm building a simple append query to add missing records to a table. It examines a list of entries, identifies which are not in the destination table, and adds them. Simple thus far. The problem comes when I add a criteria to the source side to ensure no blank entries are appended. Here's the SQL I'm trying to use: INSERT INTO tblAgents ( AgentID, AgentName ) SELECT qryAgentsImport.RecAgentID, First(qryAgentsImport.RecAgentName) AS FirstOfRecAgentName FROM tblAgents RIGHT JOIN qryAgentsImport ON tblAgents.AgentID = qryAgent...

open an excel sheet in works
Is there a way to download an excel sheet and open in works or works spreadsheet? Not that I know of. -- Don Guillett SalesAid Software donaldb@281.com "mike" <mikeext249@hotmail.com> wrote in message news:754C543F-E2EE-4B4F-9955-58682BCB14D9@microsoft.com... > Is there a way to download an excel sheet and open in works or works spreadsheet? ...

how to create 0 to 0 value charts
how can i create a chart that for the values that start say for example from 0 for a speed of 1000 and then revolve through different values of speed. these values values when plotted initially start from 0 for 1000 and reach a highest value at say 4000 and return back to 0 at 1000 speed. say that values if they look like these, 1000 0 1400 7 1500 680000 1800 0 2000 650000 2500 660000 3000 750000 3100 800000 3400 0 3500 0 3700 0 3800 0 4000 0 4300 0 4500 0 5000 0 5300 0 5500 0 5700 0 6000 0 6000 0 5700 0 5500 0 5300 0 5000 0 4500 0 4300 0 4000 0 3800 0 3700 0 3500 0 3400 0 3100 0 3000 8500...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

Use value from InputBox in Advanced ODBC Source
I'd like to include the posting date from a InputBox prompt in an advanced ODBC source. The post date would be used like select * from <complex join here> where postdate >= '<Date from Input>' and postdate < '<Date from input + 1>' The InputBox works well, I assign things to global variables, and such... Has anyone done this? If so, would you mind sharing your experience? I seem to have hit a brick wall If this cannot be done, I assume the task at hand is to create views from each query, and create simple ODBC sources, and go that way? Thanks...

I need a shortcut to make a excel file open to a specific sheet
I need to know how to modify an excel shortcut to make a file always open to a specific named "intro" sheet. You can use a macro: http://vbaexpress.com/kb/getarticle.php?kb_id=338 ************ Anne Troy VBA Project Manager www.OfficeArticles.com "EAHRENS" <EAHRENS@discussions.microsoft.com> wrote in message news:4CEF8135-5F57-400F-B5AF-9A11396110A8@microsoft.com... >I need to know how to modify an excel shortcut to make a file always open >to > a specific named "intro" sheet. Try doing it within the files workbook_open event in the ThisWork...

Blank Repeated Values
I have a list in Column A that displays multiple data in an unfilled manner. I have a list in Column B that displays multiple data in a filled manner. How do I autofill the data points in Column A? Example: A1=1 A2:A9=(blank) A10=2 A11:A19=(blank) B1:B9=Blue B10:B19=Red I do not want to manually autofill each data break because there are 30,000+ rows of data (A1:A30000) with data points starting at 1(A1) to 15,000(A29999). The format looks like a finished pivot table. It looks like I am trying to copy a finished pivot table and pasting value to another sheet, then running a pivo...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...

Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't have to always choose that option when doing a lookup? Thanks for any help. Tracey D Advanced lookups ARE the default unless you've done something to make it now so. There isn't any way to "choose" the option when doing a lookup that I know of unless you have some type of customization (easy to do) that would give the user an option. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tracey D" <...

Calling employee lookup from button through VBA code
Dear All, Can anyone show me how to call an existing GP employee lookup from a button of a modified form through VBA code. Thanks in advance. -- Developer Hi, If I'm understanding the question - you need to add the lookup button to your project and make sure your project provides that it runs on the modified form. Leslie "Dexdev" wrote: > Dear All, > > Can anyone show me how to call an existing GP employee lookup from a button > of a modified form through VBA code. > > > Thanks in advance. > > -- > Developer Hello Dexdev As per...

Call & Place Graphic Based on Cell Value?
Is there any way to call & place a graphic image based upon a cell value? Maybe you can look at J.E. McGimpsey's page: http://www.mcgimpsey.com/excel/lookuppics.html documike wrote: > > Is there any way to call & place a graphic image based upon a cell value? -- Dave Peterson ...

Limiting a column to certain values
I have a table that contains PLSS information and want to restrict the columns to certain values. Since there is a pattern in what they are restricted to, I wonder if there would be an easier way than to create a lookup table and use a constraint. For instance, my values for one field is limited to 3 characters: from 01-49, with the third character always an 'E' or 'W' Would this be easier done in a query or stored procedure or function than to create a lookup table? Thanks for your help. In the case you describe you can use a CHECK constraint: CR...

Getting Sheets Copied From One Workbook to Another Without ....?
Hello, I have a situation where I want to move 3 sheets from on workbook to another. The Problem is that the sheets appears to carr their File Path with them creating a Problem for my formulas within th destination sheet.. Is there a way to keep the Formulas in tact to represent th destination sheet? The Workbooks have the same Data, but 3 sheets from the source workboo need to be inserted in the destination workbook without paths in th formulas leading back to the source workbook (file). eg. Source workbook sheet1 A1 Reads: =IF(DAY14!B33>0,DAY14!C33,"") When it is copied ...

Vendor Lookup
One doing the vendor lookup - one user sees the 'show details' information upon lookup; other user sees the vendor list and needs to clik on the show details - how do you get the show details window to be the default option you see. Thansk! Check for full stops/periods/dots on the window title bar before or after the window name. It is possible to use VBA or modifier to open the details automatically. David Musgrave [MSFT] Escalation Engineer - Microsoft Dynamics GP Microsoft Dynamics Support - Asia Pacific Microsoft Dynamics (formerly Microsoft Business Solutions) http://www...

Pasting Values #2
Hi, I have developed a quoting tool which creates an output sheet detailing all the info, What I would like to be able to do is take a copy of this and paste the values (and Format to a new workbook). I'm currently doing this manually and I unsure how to automate it! I have never used VB which I'm guessing is the only way of doing it, so please be gentle!! Thanks, Sam. -- sammy2x ------------------------------------------------------------------------ sammy2x's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29674 View this thread: http://www.excelf...

How do you stop excel from charting empty cells/null values as zer
I'm trying to create several charts from my excel data. Several of the chart's reference cell (established range) contains formuals. When the cell's formula returns a null value, chart display's as zero. How do I setup chart to display null as nothing versus zero? The workaround for charting calculated cells when you may have missing data is to use an If formula that selects NA() if there is missing data. Something like -- = If( d2 = "", NA(), b2*d2) You'll need to replace your actual cell references and formula. This formula will place a #N/A in tho...

sheet with the contents of al the worksheet
This is a multi-part message in MIME format. ------=_NextPart_000_015F_01C869AC.0E083750 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable i am looking for a possibility to create a sheet with the contents of al = the=20 worksheets that are in the file you are working in. Input for the sheet with contents would be a specific defined cell = (let's=20 say the content is in Cell A1 of each worksheet) + the name of the tab = of=20 the worksheet. ------=_NextPart_000_015F_01C869AC.0E083750 Content-Type: text/html; charset="iso-8859-1&...

How to Copy the value of a cell to any given cell
How to copy the Value of a cell just by clicking on that cell and having its value pop on another cell. For example: A1=15, upon clicking on this cell, i would like the value to post on cell b1. What I am doing is displaying a monthly calendar, to the right of the last day of the week I have a blank cell where I currently type which day of the week someone received a paycheck and then next to this cell I enter the value of the paycheck. It would be nice just to click on the date and have it copy to the blank cell. I tried creating command button overlayed on top of the cell with the va...