#### Returning multiple values for a lookup

```I have a spreadsheet showing the purchases of various part numbers wit
the corresponding unit costs paid, there is normally more than one uni
cost per part number.  On a second spreadsheet, I want to be able loo
up a given part number in Column A and pull over the corresponding uni
costs paid into Column B.  I have no problem pulling over the firs
unit cost found, but am not able to get the second, third, etc, uni
cost found.  Can anyone help

--
Message posted from http://www.ExcelForum.com

```
 0
6/16/2004 2:11:56 PM
excel.misc 78881 articles. 5 followers.

4 Replies
211 Views

Similar Articles

[PageSpeed] 38

```Hi
do you need all individual entries or do you only want the
sum of them. In the latter case use SUMIF for this

>-----Original Message-----
>I have a spreadsheet showing the purchases of various
part numbers with
>the corresponding unit costs paid, there is normally more
than one unit
>cost per part number.  On a second spreadsheet, I want to
be able look
>up a given part number in Column A and pull over the
corresponding unit
>costs paid into Column B.  I have no problem pulling over
the first
>unit cost found, but am not able to get the second,
third, etc, unit
>cost found.  Can anyone help?
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
```
 0
frank.kabel (11126)
6/16/2004 2:37:20 PM
```I need individual entries

--
Message posted from http://www.ExcelForum.com

```
 0
6/16/2004 3:59:55 PM
```Hi,

I'm not sure if this is what you want, but here goes...  Assuming for
simplicity sake,

Sheet 1
---------

Part Number    Unit Cost A    Unit Cost B    Unit Cost C
1                          1                      2                3
2                          4                      5                6
3                          7                      8                9

Sheet 2
----------

Part Number    Unit Cost A    Unit Cost B    Unit Cost C
2                               4                 5               6

1) On Sheet 2, select B2:D2
2) Enter this formula using CTRL+SHIFT+ENTER
=VLOOKUP(A2,Sheet1!\$A\$2:\$D\$4,{2,3,4},0)

Is this what you're looking for?

In article <cfincham.17yatt@excelforum-nospam.com>,
cfincham <<cfincham.17yatt@excelforum-nospam.com>> wrote:

> I need individual entries.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
```
 0
domenic22 (716)
6/16/2004 6:54:42 PM
```Maybe a user defined function?

http://groups.google.com/groups?threadm=3EC44319.4520EADA%40msn.com

There's a link to David McRitchie's site in that post if you're new to macros.

"cfincham <" wrote:
>
> I have a spreadsheet showing the purchases of various part numbers with
> the corresponding unit costs paid, there is normally more than one unit
> cost per part number.  On a second spreadsheet, I want to be able look
> up a given part number in Column A and pull over the corresponding unit
> costs paid into Column B.  I have no problem pulling over the first
> unit cost found, but am not able to get the second, third, etc, unit
> cost found.  Can anyone help?
>
> ---
> Message posted from http://www.ExcelForum.com/

--

Dave Peterson
ec35720@msn.com
```
 0
ec35720 (10082)
6/17/2004 12:07:51 AM
 Reply:

Similar Artilces:

Query from Multiple Workbooks
Hello, I have 12 files in a directory with a sheet name Sales containing Sales data for each month having more than half a million rows in each workbook sheets The field names are all the same in all the files like date, item, itemgroup, branch, unit, salesman, amount etc I am looking for a way to get a query from all these workbook and get a summarised report based on criteria like sales by salesman, by month, by product, by product group, by date, by branch etc. Is this will be possible through code?. Itry to use MS query but not successful Using Excel 2007 and Win XP ...

Installing CRM3, multiple errors
I have installed CRM3 on a clean SBS2003 machine (under VirtualPC), but have got a series of errors and cannot now acess CRM at all. I followed the steps in the Implmentation Guide. I had to change the web site port to be 80 and the alias to be able to access it (as crm3demo). I installed CRM3 under Administrator login and was initially able to open CRM correctly, login as Administrator, go into the Administrator user and uncheck the restricted access check box. Everything appeared normal. Then I set up the sample data users in Active Directory and tried installing the sample data by ...

Varying colors on a line chart with multiple series
In Excel 2003, is it possible to change the color of part of a line on a chart with multiple data series? I can do it on a single series chart. Hi, Yes, you would need to select the segment of the line and then format it. So select the series pause and then select the segment apply formatting Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "LKP" <LKP@discussions.microsoft.com> wrote in message news:CB5CF5B8-5E3C-4288-A7B1-63958DB0C688@microsoft.com... > In Excel 2003, is it possible to change the color of part of a line on a > chart with ...

match dates in two columns get value from third
I have a data set on daily basis and another data set on weekly basis. i want to put the weekly data in daily data set by putting a formula which picks values from col c for relevant dates in daily set. example - let us say this is the data set ColA Col B Col C jan1 jan1 8 jan2 jan7 7 jan3 jan4 jan5 jan6 jan7 I want the data from colC against dates in ColA as in ColD below. it should leave other cells blank (colA and ColB are dates format) ColA Col B Col C ColD jan1 jan1 ...

Copy and paste formula; Value pastes instead
Excel 2002 I've tried starting several worksheets, checking formats, pasting special (formula only). For some reason the formula actually copies OK by looking at the formula bar, but the value in the cell is identical to the value of the copied cell.Incorrect. Tried even the simplest formulas with no joy. BK ...

OL2003 Comining Multiple Contacts
As I mentioned in another post, I'm playing around with Outlook. Here's my Contacts problem. I was using Outlook at work and synchronizing my Contacts with my Palm Pilot. So that part of the transition was a breeze. But I was using a completely different email program at work which has it's own address book. I know how to import that address book into Outlook, but the end result will include be a whole bunch of partial duplicate entries where say, I have name and address information in the old Palm/Outlook database but only an email address in the email address book. Any sugge...

setting up multiple hotmail accounts not possible
I am trying to set up 2 Hotmail accounts in Outlook 2002. When I have entered all the correct information and try to Send/Receive, after about a minute, it gives me an error message saying that it needs to be in offline mode. My original account works fine and does even with the second added. But when I add the second e-mail address, it doesn't send or receive from that new account. Does anyone have any ideas on how to fix this? It would be greatly appreciated. Steve Outlook does not support more than 1 account - some people have 2 or more working but it seems like the luck ...

Combining multiple click events
Is there a way to combine multiple click events into one relatively short function? What i have right now is a form with two sets of checkboxes. Each set is about 25 check boxes. When the form loads, the most commonly selected ones are automatically checked. But to save time, I also added a "select all" button for each set of check boxes. When you click it, it will auto check each of the 25. Uncheck it and all 25 are unchecked. What I want to happen is if I click the "select all" checkbox and then uncheck one of the 25 options, the "select all&qu...

Nesting Lookup Functions
Is it possible to nest an HLOOKUP in a VLOOKUP equation? Here's what I have: =VLOOKUP(C27,A2:B5,(HLOOKUP(D27+1,INDIRECT(A2),21) Each VLOOKUP lookup value is the start of a range that I would like to be the first row of an HLOOKUP range. I might be going about this the wrong way, so if you have suggestions, I'm definitely open to it!! The objective is to provide Excel with two associated values (i.e., department and production value in a given range) and have Excel produce the production value ranking. I have a ton of departments each with a range of production valu...

How do I eliminate multiple task status reports in Outlook?
In Outlook, if I keep a task open for several minutes while working on it, it sends a Task Status Report to the person who generated the task every three minutes. I'd like to turn this off so that the only time anyone gets a report is when I click Send Status Report. ...

Change font size based on value of a cell
If the value of A1>0, I need the font size in a merged cell to change from the default 10 to 16. It needs to return to the default size when A1 returns to a value of 0. Can someone help with this? Thanks. Michael Here's a little macro that will do it....... Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Range("a1").Value = 0 Then Range("B1:E3").Font.Size = 10 Else Range("B1:E3").Font.Size = 16 End If Range("B1").Select End Sub Vaya con Dios, Chuck, CABGx3 "MichaelRLanier@gmail.com" wrote: > ...

Chart Value Changes in VBA
When you have say a bar chart and drag the bar downwards this will change the value in the reference source cell. Is there a way through VBA to identify the source and the cell changed by when dragging the bar. That is, when you drag the bar in the chart let me know the cell that ahs changed! Any help would be appreciated. Thanks in advance. Howard John, If your objective is to prevent the cell changes via the chart, you can protect the worksheet (Tools - Protection). The cells should be locked (Format - Cells - Protection). Or you can use the Chart_SeriesChange event macro for the cha...

p-value, statistics
I want to determine the p-value of a regression line. I hope someone can explain to me how i can do this in excel. -- wim rademakers ------------------------------------------------------------------------ wim rademakers's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30134 View this thread: http://www.excelforum.com/showthread.php?threadid=502248 wim rademakers - > I want to determine the p-value of a regression line. I hope someone can > explain to me how i can do this in excel. < One way is to use the Regression tool, part of the Analysis Too...

sum negative and positive values separately
Hi, I have a time series like this: 1995, -.05 1996, -2.1 1997, 3.6 1998, 4.3 1999, -0.7 2000, -0.08 and I would like to sum just the positive and negative value separately in this way 1995, -2.15 1997, 7.9 1999, -0.78 And I don't know if it is To sum only positive values use =SUMIF(B1:B10,">0") and to sum only negative values =SUMIF(B1:B10,"<0") But I cannot see how you get the second tables from the first. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "bora" <bora@unisi.it> wrote in message news:1...

Currency values set to 2 decimal places
Is it possible to set a currency value fixed to 2 decimal points a opposed to just rounded up or down. for example if 33.3% discount was applied to �10 it would be �6.66666 which would display as �6.67 Therefore if you had 3 x �6.67 it woul work it out as �20.00 and not �20.01. Pau -- clappu ----------------------------------------------------------------------- clappus's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1613 View this thread: http://www.excelforum.com/showthread.php?threadid=27569 Yes, set the format to Currency, and that will work. -- HTH R...

List Box Values
Can I use values from a list box/combo box in a formula and if so how do I do that? TIA Sorry wrong NG "Mike Cramsey" <mcrams@adams.net> wrote in message news:%23b6IdNn1EHA.3908@TK2MSFTNGP12.phx.gbl... > Can I use values from a list box/combo box in a formula and if so how do I > do that? > TIA > ...

Setting AutoNumber to Non-Default Value
I would like to use the AutoNumber data type within a field. However I would like the sequential autonumbering to begin from 6000 rather than the default value of 1(for the first record). Thanks for your help in advance. Jeff I suggest you may not want to use Autonumber for that use. Autonumbers are designed to provide unique numbers. It in not designed to provide numbers in order and for a number of reasons may not do so. As a result using them in any application where the user sees the numbers is likely to end up with confusion. There are other ways of providing the numbers...

Multiple workbooks open -- closing workbooks
Dear Clever People, I hope this is not the "silly question of the day". We were wondering (when you have multiple workbooks open and you close one workbook) why are all other workbooks forced to be closed, too? Is it possible to ever close one workbook and leave all the others open? Thank you, Janet Close it by going to file>close or by pressing Ctrl + F4 or by clicking the smaller X below the big red X -- Regards, Peo Sjoblom "Janet Panighetti" <JanetPanighetti@discussions.microsoft.com> wrote in message news:1C9BC30B-BE1A-4605-B215-99C07CB1D366...

Extended Pricing price groups should allow multiple Units of Meas.
We categorize our items without regard to units of measure, and want to make an overall change to a particular category of items, e.g. increase the discount percent for all items in that category. But we cannot group these items into one price group because they have different units of measure. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft ...

Outlook 2003 opens multiple instances
When I double click the desktop icon for outlook it opens twice or has two instances available in the task manager window. Once I disable or end these and reopen outlook it is fine. Also, we are currently running Viewmail and Viewfax from Active Voice. Any help would be grealty appreciated. Charles Garcia wrote: > When I double click the desktop icon for outlook it opens > twice or has two instances available in the task manager > window. Once I disable or end these and reopen outlook it > is fine. This happens randomly for me too. In fact, for a long time it used to a...

Mult-Value Select returning ID# not text.
I have a text field that is based on what is selected in a combo box. The text field is populating but it is returning the ID numbers and not the text. The combo box is based on a query from a form that has a combo box field that allows Mutli-Value selections. I tried basing the field on the uderlying table but then it only returns the one value and not the mutl-values that have been selected. Try this -- Open form in design view, click on combo, double click on combo, scroll to near the bottom, select Properties. Scroll to Column Widths and change it form something like t...

Return Column header, if row value is > X
I need to create a formula the looks at a row range for a value over \$1,000,000 and if a cell in the row matches the criteria, return the header of that column that matches the cell... For example A B C D 1 "What FY contains value >1,000,000?" __________ 2 FY01 FY02 FY03 FY04 3 1,500 15,000 150,000 1,500,000 DESIRED result in cell D1 should be FY04. Any solutions on how to write this formula? I'm sure it will be a nesting stat...

Upgrading Multiple Companies at Different Times
I have multiple companies currently in my GP 8.2 installation on a single server. Of course these companies each share the DYNAMICS database. I am plotting out the upgrade process to 9.0 and would like to upgrade one company at a time, as one of them is ready to go, while the other will require more work due to customizations. Would there be any issue with upgrading CompanyA to 9.0 on a new server, and then a month or two down the line, upgrading CompanyB to that same server? Would CompanyB be able to slide right in to using a DYNAMICS database that it had not used for a couple of...

return record if 5th character is a letter
Hi, I have the mid statement to pull the 5th character, but I need the (query) syntax to filter out the ones that are a letter (alpha) -- Thanks, Ron UCase(Mid(MyString, 5, 1) >= "A" AND UCase(Mid(MyString, 5, 1) <= "Z" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Ron5440" <Ron5440@discussions.microsoft.com> wrote in message news:D3BED359-DBC1-4DFE-94F0-BA7FAF257F8E@microsoft.com... > Hi, > I have the mid statement to pull the 5th character, but I need the >...

Avoid 0 values in a graph?
Hi everyone, i have a graph based on a pivot table with countries and production as fields. What i need is the graph not taking into account the countries with production=0% so those countries would not appear in it. Any help would be greatly apreciated. Thanks! -- daru ------------------------------------------------------------------------ daru's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34656 View this thread: http://www.excelforum.com/showthread.php?threadid=544228 ...