Return a name in the same row

I have a 2 sheet spreadsheet. I want a formula to match a cell in column A of 
sheet 1 to a matching cell in column a of sheet 2 and return a name form the 
same matching row in column C of sheet 2. I have searched for this formula 
and cannot find it. Please help.

Scott

Sheet 1

CustNum
1
9
9
9
9
10
10
11
11
11
11
11
14
14
30
38
38
40
50
50
50
50


Sheet 2
CustNum	ShopNum	LastName
1	5	Name one
2	5	Name two
5	53	Name three
7	57	Name four
8	8	Name five
9	9	Name six
10	10	Name seven
11	11	Name eight
12	340	Name nine
13	12	Name ten
14	13	Name eleven
16	15	Name twelve
18	17	Name thirteen
19	18	Name fourteen
20	19	Name fifteen
22	21	Name sixteen
23	22	Name seventeen
24	23	Name eighteen
26	25	Name nineteen
27	26	Name twenty
28	27	Name twentyone
29	28	Name twentytwo

0
Utf
11/18/2009 5:26:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1026 Views

Similar Articles

[PageSpeed] 56

Try the below in Sheet1 with Custnumber in A1
=VLOOKUP(A1,Sheet2!A:C,3,0)

If this post helps click Yes
---------------
Jacob Skaria


"Scott J" wrote:

> I have a 2 sheet spreadsheet. I want a formula to match a cell in column A of 
> sheet 1 to a matching cell in column a of sheet 2 and return a name form the 
> same matching row in column C of sheet 2. I have searched for this formula 
> and cannot find it. Please help.
> 
> Scott
> 
> Sheet 1
> 
> CustNum
> 1
> 9
> 9
> 9
> 9
> 10
> 10
> 11
> 11
> 11
> 11
> 11
> 14
> 14
> 30
> 38
> 38
> 40
> 50
> 50
> 50
> 50
> 
> 
> Sheet 2
> CustNum	ShopNum	LastName
> 1	5	Name one
> 2	5	Name two
> 5	53	Name three
> 7	57	Name four
> 8	8	Name five
> 9	9	Name six
> 10	10	Name seven
> 11	11	Name eight
> 12	340	Name nine
> 13	12	Name ten
> 14	13	Name eleven
> 16	15	Name twelve
> 18	17	Name thirteen
> 19	18	Name fourteen
> 20	19	Name fifteen
> 22	21	Name sixteen
> 23	22	Name seventeen
> 24	23	Name eighteen
> 26	25	Name nineteen
> 27	26	Name twenty
> 28	27	Name twentyone
> 29	28	Name twentytwo
> 
0
Utf
11/18/2009 5:52:02 PM
Try this in B2 on Sheet1:

=VLOOKUP(A2,Sheet2!A:C,3,FALSE)

and copy B2 down as needed.

This version returns an empty string (the cell looks empty) if the CustNum 
is not found on Sheet2:

=IF(ISERROR(VLOOKUP(A2,Sheet2!A:C,3,FALSE)),"",VLOOKUP(A2,Sheet2!A:C,3,FALSE))

Hope this helps,

Hutch

"Scott J" wrote:

> I have a 2 sheet spreadsheet. I want a formula to match a cell in column A of 
> sheet 1 to a matching cell in column a of sheet 2 and return a name form the 
> same matching row in column C of sheet 2. I have searched for this formula 
> and cannot find it. Please help.
> 
> Scott
> 
> Sheet 1
> 
> CustNum
> 1
> 9
> 9
> 9
> 9
> 10
> 10
> 11
> 11
> 11
> 11
> 11
> 14
> 14
> 30
> 38
> 38
> 40
> 50
> 50
> 50
> 50
> 
> 
> Sheet 2
> CustNum	ShopNum	LastName
> 1	5	Name one
> 2	5	Name two
> 5	53	Name three
> 7	57	Name four
> 8	8	Name five
> 9	9	Name six
> 10	10	Name seven
> 11	11	Name eight
> 12	340	Name nine
> 13	12	Name ten
> 14	13	Name eleven
> 16	15	Name twelve
> 18	17	Name thirteen
> 19	18	Name fourteen
> 20	19	Name fifteen
> 22	21	Name sixteen
> 23	22	Name seventeen
> 24	23	Name eighteen
> 26	25	Name nineteen
> 27	26	Name twenty
> 28	27	Name twentyone
> 29	28	Name twentytwo
> 
0
Utf
11/18/2009 5:53:02 PM
Thank You Tom and Jacob. It worked fine.

"Tom Hutchins" wrote:

> Try this in B2 on Sheet1:
> 
> =VLOOKUP(A2,Sheet2!A:C,3,FALSE)
> 
> and copy B2 down as needed.
> 
> This version returns an empty string (the cell looks empty) if the CustNum 
> is not found on Sheet2:
> 
> =IF(ISERROR(VLOOKUP(A2,Sheet2!A:C,3,FALSE)),"",VLOOKUP(A2,Sheet2!A:C,3,FALSE))
> 
> Hope this helps,
> 
> Hutch
> 
> "Scott J" wrote:
> 
> > I have a 2 sheet spreadsheet. I want a formula to match a cell in column A of 
> > sheet 1 to a matching cell in column a of sheet 2 and return a name form the 
> > same matching row in column C of sheet 2. I have searched for this formula 
> > and cannot find it. Please help.
> > 
> > Scott
> > 
> > Sheet 1
> > 
> > CustNum
> > 1
> > 9
> > 9
> > 9
> > 9
> > 10
> > 10
> > 11
> > 11
> > 11
> > 11
> > 11
> > 14
> > 14
> > 30
> > 38
> > 38
> > 40
> > 50
> > 50
> > 50
> > 50
> > 
> > 
> > Sheet 2
> > CustNum	ShopNum	LastName
> > 1	5	Name one
> > 2	5	Name two
> > 5	53	Name three
> > 7	57	Name four
> > 8	8	Name five
> > 9	9	Name six
> > 10	10	Name seven
> > 11	11	Name eight
> > 12	340	Name nine
> > 13	12	Name ten
> > 14	13	Name eleven
> > 16	15	Name twelve
> > 18	17	Name thirteen
> > 19	18	Name fourteen
> > 20	19	Name fifteen
> > 22	21	Name sixteen
> > 23	22	Name seventeen
> > 24	23	Name eighteen
> > 26	25	Name nineteen
> > 27	26	Name twenty
> > 28	27	Name twentyone
> > 29	28	Name twentytwo
> > 
0
Utf
11/19/2009 11:49:01 AM
Reply:

Similar Artilces:

Question on defining a name
If I select <Insert><Name><Define> I can define something e.g (=0.95) as, say, discount rate. Is there a limit to how many defintions you can make? For example, I have made 13 definitions in the define dialog box but cannot make anymore. They simply do not appear. Am I to assume that 13 is the limit? All help welcome. Alex, As far as I know, there is no limit on the number of names in a workbook. I've done several projects with many thousands of names each. I don't know why you are having the problems you describe. -- Cordially, Chip Pearson Microsoft MVP -...

Customer Returns
I'd like to know if there someone has written a RMS report that shows the customer account number, customer's name (last, first), and other customer statistics that INCLUDE pieces purchased and pieces returned. Or, if someone with more knowledge of SQL would care to share their coding expertise to accomplish the same, it would be appreciated. ...

multiply a row by a certain number?
i am having trouble with excel i want to multiply this row by 1.4 and make it appear next to it if anyone can help thank you if you mean multiply each item in a column then this will work. Modify to suit Sub multiplyall() For Each c In Range("c7:c10") c.Offset(, 1) = c * 1.4 Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "multiply a row by a certain number?" <multiply a row by a certain number?@discussions.microsoft.com> wrote in message news:C84A6F67-03CD-4902-9760-36051A179831@microsoft.com... > i am having trouble with excel i want to multipl...

Return-Path: <> from Postmater
Hi, I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive bad mail the response is made from postmaster@mydomain.com but the return-path field is blank. With some anti-spam filter this Email is Junk. Return-Path: <> Anybody know how to specifeid a return-path to the NDR mail? _____________ Thank you Sebastien On Fri, 4 Nov 2005 12:48:04 -0800, "Sebas" <Sebas@discussions.microsoft.com> wrote: >Hi, > >I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive >bad mail the response is made from postmaster@mydomain.c...

Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard Orders? Yes. You must make sure to change the site when performing a Return Transaction. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Everton Raymond" wrote: > Is it possible to have a different default Site ID for Returns than Standard > Orders? Sorry, I did not see the word "default" in your post until after I r...

changing extension names
I did try that. I can't remember the exact complication but it did not work. Want to quote your original post? -- Roady [MVP] www.howto-outlook.com Tips of the month: -Creating Signatures -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Cap't Bob" <anonymous@discussions.microsoft.com> wrote in message news:1169501c441aa$e2f94810$a401280a@phx.gbl... >I did try that. I can't remember the exact complication > but it did not work. ...

Query regarding A,B,C char. width values returned in 16-bit and 32-bit OS
I am trying to retrieve individual character widths of a string (from a TrueType font) and I tried using GetCharWidth (which weirdly works for TrueType fonts too tho' MSDN says otherwise) and/or GetCharABCWidths to retrieve the A,B and C spacings. Please note that I have selected the TrueType font (hFont) into the Device Context (hDC). I used GetCharABCWidths. Problem is this: GetCharABCWidths returns correct A,B and C widths in Win 98 and incorrect (much lesser A,B and C widths such that the characters OVERLAP) in other 32-bit OS (NT, XP). The effect is seen across all TrueType fonts, th...

Report for Returns
Anyone know a why to report on frequency of customer returns -- so we can see if any customers are making habit of this? Thanks and regards, Rich hitman, Try running a detailed sales report filtered to only negative sales displayed. Good luck, Matt "hitman" <hitman@discussions.microsoft.com> wrote in message news:0B3EE780-2655-45B4-B3C6-1F5D35FFA2A7@microsoft.com... > Anyone know a why to report on frequency of customer returns -- so we can > see > if any customers are making habit of this? > > Thanks and regards, > > Rich ...

Store the full path and document name in a hyperlink
Hi I need to create an Access database to track a number of documents that are created and stored during the tendering process. I have created a number of tables with hyperlink fields that will store the information eg Tender proposal document (a Word doc); Tender budget document (an Excel book) and company profiles (pdf) from companies submitting tenders. To create the hyperlink to each of these documents in the form, at the moment I right click, choose Hyperlink, Edit Hyperlink and navigate to the required document and select it. The path and filename is then stored in the field, which is ...

Returning the Beginning of the week
Returning the beginning of the week. e.g. if the date == 02/03/04 (Friday 2nd March 2004) the beginning week for that date == 01/03/04 (Thursday 1st March 2004) Hows does one go about achieving this? Regards -- Rizitsu ------------------------------------------------------------------------ Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15840 View this thread: http://www.excelforum.com/showthread.php?threadid=314968 This formula will take a date in cell A1 and give the start of the wee in B1 * assumes that monday is the start of the week, so i...

File Names
What is the name of the Sent file in Outlook. I accidently deleted it and want to retrieve it. Thanks There is no file specifically for a Sent item. All data is stored in a pst-file. Is it no longer in your Deleted Items folder? -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Create your own fully customized Toolbar -Creating a Classic View in Outlook 2003 Subscribe to the newsletter to receive news and tips & tricks in your mailbox! www.sparnaaij.net (I changed my reply add...

Invalid object name "ECDSMTP"
We are receiving this error message when tryin to merge parts with Preofessional Services Tools Library: [Microsoft][SQL Native Client][SQL Server] Invalid object name "ECDSMTP" When you click OK the following error message occurs: The stored procedure TACallITem Combiner returned the following results: DBMS: 208, Dynamics: 0 I have searched the Knowledge base and have not found any help with either error message. We use Dynamics GP ver 10 SP1 Mike Mike, The reason you usually won't find anything in the KB for these kind of problems is due to the first part of the e...

return
Capability of the system to automatically change the status of the PO to change order, and add line items that have been returned. ---------------- 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 Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.ms...

Deleting rows 05-03-10
I'm trying to figure out if I can set up a macro to delete rows if they do not contain anything in specific columns. For example, I have product codes in column A and totals for on-hand and ordered numbers in columns B and C. If some rows do not have on-hand or ordered numbers I want to delete those rows. Any ideas? Start here http://www.rondebruin.nl/delete.htm For example in the first macro use Change the range in this line that you want to test If Application.CountA(.Cells(Lrow, 1).Range("A1,M1,X1")) = 0 Then .Rows(Lrow).Delete Sub Loop_Example() ...

xpath
I'm using the following xpath in .net //Station[@name="Station1"]/WI/Boards/Board[@sn="12345"][not(@finish)]") to test if there is a Board element with NO "finish" attribute. If there is, I need to get the @title of the WI element...but haven't been able to figure it out yet (and yes, I do user XpathVisualizer!). Also, how can I test if the above xpath results in no/null nodeset? It doesn't seem to work for me unless I first set an attribute.value string to test (e.g., Dim unfinished As String = n1.Attributes.GetNamedItem("start").Va...

Alpha sort names/numericals
I have 2 columns. 1st column has names, 2nd has numerical values relevant to the names. How can this data be alpha sorted and keep the same name/numerical data. data/sort "dane" <dane@discussions.microsoft.com> wrote in message news:A9565E66-9FC0-48BF-A06B-FF51E184A8A1@microsoft.com... > I have 2 columns. 1st column has names, 2nd has numerical values relevant to > the names. How can this data be alpha sorted and keep the same name/numerical > data. Hi Dane, Please state clearly what the problem is. If you sorted and got the names sorted and the numbers did not...

Wordwrap and AutoFit Rows
Hi All! Question: Is there a maximum number of characters that a single cell will accept? I have this well with a really really long paragraph in it and it's not showing the last few words in the sentence... Also, is there a way to format the sheet so when I change the column width the autofit row function will automatically update the contents of the cells? ... As it is now, when I make the column wider, I'm having to click into each cell and hit enter so that it adjusts the row height... Thanks in advance! LavaDude... Try adding alt-enters every 80-100 characters. Yo...

form creating new row in table1, looking for field value in table
Please can someone help as I have been battling all week with this problem, and haven't had a response. I have a form which uses some data from table1 to create an entry in table2 (i know duplication is bad, but as this is a live database, and i am working on a new section, this makes this complicated task easier), which works fine. however, i have a third table that has the unique ID from table1 and table2 in order to show when a table1 row of data is needed in table2 via a checkbox. This third table3 is created when elsewhere. The problem lies with bringing in the unique ID value...

File Name Problem
I created a spreadsheet in the usual way, when I use the spreadsheet the file name adds a :2 following the name.xls. When I close the file the :2 disappear. This is not causing me any problems I've not seen this before and curious if it's something I've done. Thank you check <Window> and see if there is a "...xls:1" you may just have two windows for the same file showing if you close one of them and save the file the :2 should go away. "Philg" wrote: > I created a spreadsheet in the usual way, when I use the spreadsheet the file > name ad...

Return
When returning an item, can you return the item by selecting the matrix rather than the individual item and changing the quantity to -1? Hi KIm, Normally this won't work as it works in sale where for each matrix type you can put the qunatites. HOWEVER when you select the matrix item it bring the matrix window but allow only one item at a time to be returned and make the -1 qty automatically in return document type. It work but one by one. Regards Akber "Kim" wrote: > When returning an item, can you return the item by selecting the matrix > rather than the individua...

Returning Multiple Values in a Single Cell
Hello, I posted this same question a couple of weeks ago but forgot to mention a key point. My question was as follows: I have read a few posts regarding this subject but am still somewhat lost. I have a two sheet workbook (Excel 2000). On the first sheet is a list of projects that my department will complete during the year. A description of each project is given, a start date, an end date, as well as which crew will be completing the work (Crew 1 or Crew 2). What I am trying to do on the second sheet is to be able to type in any date, and have the description of the project on that da...

Returns immediate function
Hi all, I know that the function CeRapiInitEx returns immediately. No wait for processing. I want to implement such a function. Returns immediate. no wait. How do I implement such a function? Thanks Ko You might create a UI thread that do the work, launch it in the "non-wait" function and return immediatly to the caller. When the thread done it's works, you can post an application message to return the results.... Greetings, Gaetano Sferra "PPC DEV" <ppc_dev@yahoo.com> ha scritto nel messaggio news:033101c3b388$a7098910$a301280a@phx.gbl... > Hi all...

Using a named range in a Sumproduct comparison
Hi, Lets suppose I have in a worksheet of 500 rows of data with 4 columns. I want to apply sumproduct for determining count with certain conditions. Now one of the columns (column A) have data such that it is always only one of the following 6 possible values. "UK", "US", "IN", "AU", "FI" and "PA Now one of the conditions in the sumproduct is that column A has to be "UK", "AU" or "FIN". For one other column the (Column B) there are 4 possible conditions like 56, 78, 89 or 44. Now the next condition for ...

Multiple display names for one user?
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C42D19.8EED3880 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is this possible? We have a few people that I like to call "special" = who seem to have some identity problems. These people have a first = name, middle name and a nick name. Well they don't like the fact that = their email shows up as their legal name, but they still want it there = incase people use it. So what we have ended up doing is creating = multiple logins and email accounts th...

Is there a way to increment the name of a cell?
Merry Christmas everyone!!! Was wondering if any of you can help me with this. I have a financial model that currently has numerous named cells in the format of XXX_03 ( the 03 is to denote 2003 ) but I am making 2004 projection and therefore am wondering if there was an easy way to sort of copy and paste the names into a new column and automically renaming cells by an increment of 1 to XXX_04. If anyone can help, it will be greatly appreciated! Thank you Justin maybe edit/replace _03 with _04 -- Don Guillett SalesAid Software donaldb@281.com "Justin" <jsim_hba2003@h...