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
1217 Views

Similar Articles

[PageSpeed] 42

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:

Unique account name
Hi All! Our client wants us to provide functionality of checking unique during entering a new account. What solution can i provide or how to argue out of this requirement? I can see following: 1. Using post-callout for checking and then sending notification by e-mail in problem case. 2. Make primary key for this field in DB. (Some hacker's method) 3. Or convince customer that in case not bulk entering new clients. Before enering a new account employee can check via advanced find if it exists or not. What thoughts? I find it interesting that you have the employee first looking for the re...

User married-name change
I've read the posts from others but have not seen the complete answer. I have Exchange 2003 in Windows2003 AD environment. Is there a best practice for changing everything over when a user gets married. I changed everything in AD, added the the new email address to her profile setting it as primary. In Exchange System Manager, her mailbox still shows as her old name and emails she sends still show as old name. Any way to change this? Regards, -Karl One of the things that has come up before is that the rename should occur via right click on the user account in ADUC and then choose...

Email display names in Outlook 2000 SR-1
I send email to a single recepient or multiple recepients that are listed in my contacts folder. When the message is addressed, I'd prefer the name in the To: field shows the name of the person, not their complete email address. Is this a view setting in the contact or the address book? Is this related to a resolved or unresolved name setting? I'm trying to prevent the full email address from being displayed to protect from spam when forwarded and viewed by non-Outlook recepients. It is related to the Display Name, which you can only access and change manually in Corp/Workgr...

Documenting Smartlist Reports beyond report name
Is there a way to document within GP further information about smartlists. I would like to be able to write a 2 or 3 sentence explanation of the uses of the report, etc. that would be readily available such as in a report properties but I don't see any such capability. ...

Insert row with button
Is it possible to have a main workbook with information from other workbooks at each worksheet? I have 20 workbooks with information. I would like to haven a main workbook with on sheet1 every row from the 20workbooks with category1, on sheet2 every row from the 20workbooks with category2, etc, etc. The information in the 20workbooks in always only on sheet. The Colum in which the data is has no fix end. I go to fill in the rows trough a user-form and comes always under the last row. The mainsheet has to update every time it opens (and if possible a button on the sheet to update man...

Need single column/row of numbers to print down then across pages in order
I have a very long list of phone numbers that occupies a1,a2, so on. approx. 45,000 records. I need to be able to print hard copies of these numbers so that it stays in order from page to page but is not just one column. (as to cut the # of pages from 300 to 50 or lesss) In other words, start in the top left corner of page through to bottom, next row, and so on. I thought there would be a way to set this up in printing options but to this point haven't figured it out. I have the same list (1 row again) also in Access if it would be easier. Would VERY much appreciate any clu...

8000 byte row limit blown while adding fields to Microsoft CRM Account object.
Hi all My customer has requested a great deal of customisation on their CRM implementation, particularly the Account object. As such, around 40 new fields have been added to the account object via the Deployment Manager. Unfortunately, we are at the point now where additional fields cannot be added, due to us reaching the 8000 byte row limit of SQL Server! The message in the event log is as follows: 'dmLog: New size of the attribute ({B806C9CD-554F-4BA6-80C2-0906640276BE}) exceeds the SQL Server row limit of 8000 bytes.' This really leaves us in a bit of a pickle, because: a) We n...

Using a NAME as a reference in another range
* The value in cell A1 varies depending on the number of rows of data brought down in a query * I have used the 'NAME' function to name cell A1 '_DataRows' I now want to use '_DataRows' to identify the end of a range in a SUMPRODUCT formula. (i.e. a formula something like this...) =SUMPRODUCT (--($D$1:$D$"_DataRows"=XYZ),--($D$1:$D"_DataRows"<>""),--($E$1:$E"_DataRows")) How do I do this (see examples below). Many Thanks, Craig ------------------------------------------- Example 1: --------------- If cell A1 = 2...

Table names in recordsets?
If I have an SQL call e.g. SELECT * FROM Customers In the returned recordset, how to I access the data with the table name e.g. rs("Customers.ID") rs("Customers.Name") rs("Customers.Address") I can only get it to work without the table name e.g. rs("ID") rs("Name") rs("Address") My SQL string can change at runtime, and I may have duplicate field names so I want to make sure I'm accessing the correct field from the correct table. Thanks Jon Then why not alias the duplicate field names in your SQL? Alternatively, you can re...

Rows in columns
The cells in Col A contain the following data: ASMNT 2005 00000 TOTAL PAID SITUS PAREN ASMNT 2005 2006 00000 28527 TOTAL PAID SITUS PAREN ASMNT 2005 00000 TOTAL PAID SITUS PAREN ASMNT 2004 2005 00000 TOTAL PAID SITUS PAREN ASMNT 2005 2006 00000 TOTAL PAID SITUS PAREN ASMNT 00000 28527 TOTAL PAID SITUS PAREN ASMNT 2005 2006 00000 28527 TOTAL PAID SITUS PAREN How can I "transpose" the cells to look like this: : ASMNT ASMNT 2005 00000 TOTAL PAID SITUS PAREN 2005 00000 TOTAL PAID SITUS PAREN ASMNT ASMNT 2005 2006 00000 28527 TOTAL PAID SITUS PAREN 2005 2006 00000 28527 TOTAL PAID SITU...

SelectNode not returning any nodes
I thought I knew XML but this just doesn't make sense. I have a dataset ds. I load the data from the dataset into an XML document and try to SelectNodes. Nothing. Here is the code Dim docMap As New XmlDocument() Dim nodeList As XmlNodeList docMap.LoadXml(ds.GetXml) nodeList = docMap.DocumentElement.SelectNodes("/Mappings/Map") Here is what I get when I do a ds.GetXml <Mappings xmlns="http://emp.com/namespace"> <Map> <HQuote>$INDU</HQuote> <TC2000>dj-30</TC2000> ...

How to create list of (unique) street names ?
We want a list of all the streets in our data base. How can we create a list of individual street names in our data base? We do not want the name of a street to show more than once on the list... So I guess we want to do a query to find 'unique' or 'individual' street names. (Or do we need a report built on a query?) Thanks for any help. Assuming you have a field named Street in Table1: 1. Create a query using Table1. 2. Drag the Street field into the query grid. 3. Open the Properties box. Looking at the properties of the query (not of a field), set Unique Values t...

How do you change the column names,in Excel?
I am making a chart and I need to change the column names in Excel. I use to know how, but seem to have fogotten. Does anyone know how?? Thank you, Angel :0) If you mean the grey buttons (A, B, C...) at the top of the worksheet, they can be changed to numbers (1, 2, 3...), but you can't create your own column headings. Instead, you can add your headings to the first row in the worksheet. Then, select row 2, and choose Window>Freeze Panes The heading row will stay locked in place as you scroll down the worksheet. flutterfly wrote: > I am making a chart and I need to change the ...

Make a name = column cell range in table?
I have a table with a header containing names for each column. Somehow I ended up with names defined for each column, but the range doesn't automatically expand to encompass the full column height (when I increase the rows in my table). Is there some means of configuring things so the column names appearing in my table header will automatically be defined? Check out this web page of Debra Dalgleish: http://www.contextures.com/xlNames01.html#Dynamic -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGro...

A/R report with customer name and address.
Hi, I would like to create an accounts receivable detailed report that displays the customers name and address but I need the name and address to be grouped together as in a mailing label rather than being individual coumns. Thanks. ...

inserting a row WITH any needed formulas
I made a check register sheet, with a formula placed in the 'balance' column that combines entries in the 'debit' or 'credit' columns to the running total in the cell above it for its new value. This, of course, is a popular spread sheet application (new for me, though). I generated the formulas in the 'balance' column by copying, so that relative cell addresses created going down the column. I ran into a problem when I tried to insert a new row in the sheet where an entry had been omitted. The new row had no formula in it. I had to unprotect the sheet, the...

header row
what is used to keep certain cells(such as a header row) visible while scrolling? TAZ Select the cell immediately to the right and below where you wish the freeze to be and select Window/Freeze panes. Andy. "TAZ" <anonymous@discussions.microsoft.com> wrote in message news:0d2f01c39fb5$479f6030$a101280a@phx.gbl... > what is used to keep certain cells(such as a header row) > visible while scrolling? ...

Number of rows which are part of a group/outline
Hello, I use the outline/group-functionality very extensively so that I always use 3-5 levels of them. Now in my program it is necessary for me to know how many rows are part of an outline. So I just closed the group, counted the number of visible rows, opened the group and again counted the number of rows -> the difference then is the number of rows inside the group/outline. But now I recognized that it no more works if inside of that group again groups exist which are closed. Then all the rows of this group/outline are just counted as one row, but not the number of all the rows inside o...

How to feed the contents of a cell as row index?
Hi, eg. if c5=3 a[c5] should give me the contents of a3 like in any high level language Excel does not treat a column as an array (table) so when C5=3, the formula =A[C5] means nothing. However, the formula =INDIRECT("A"&C5) will be equivalent to =A3 when C5 has value 3 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "sudhakar" <sudhakar@discussions.microsoft.com> wrote in message news:CD83DC2E-7213-438E-A7CD-F3C5DC7463F4@microsoft.com... > Hi, > eg. if c5=3 > a[c5] should give me the contents of a3 li...

Is it possible to high light a row of cells
I have columns from A1 to A300 TO CP1 TO CP300 if I click on a particulr cell on any row is it possible to high light that whole row if there is a way please tell me how!!! On dec. 10, 04:23, Mike <M...@discussions.microsoft.com> wrote: > I have columns from A1 to A300 TO CP1 TO CP300 if I click on a particulr cell > on any row is it possible to high light that whole row if there is a way > please tell me how!!! You need an event sub for that like this one: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A1:F10&...

Worksheet Specific Range Name
How do I define a range name on a worksheet that is specific to that worksheet? i.e. I want to use the same range name on several worksheets. Regards, On Oct 19, 10:53 am, Alan1 <alan.do...@virgin.net> wrote: > How do I define a range name on a worksheet that is specific to that > worksheet? i.e. I want to use the same range name on several > worksheets. > > Regards, 1 Select the range of cells that you want to name. 2 Choose Name from the Insert menu, then choose Define. Excel displays the Define Name dialog box. 3 In the Names in Workbook box, enter the name of the c...

How To Copy Rows without Work Art
I have some Word Art in say Rows 9-23, I use some Macro code to copy cells 9-23 down to 10 grouped rows below 23, but everytime I run the Macro it also copies a 'New' Work Art over the existing Art below Row 15. My fear is that my file size will balloon. My code simply states Sheets("Starters").Select Rows("9:23").Select Selection.Copy Range("A25").Select ActiveSheet.Paste Range("A41").Select ActiveSheet.Paste How can I copy Rows 9-23 without the WorkArt also (which is 'floating' over Rows 14-18) Thanks Anot...

Sorting rows of repetitive data
I have 12 months annual line item spend data and within that the same part number repeats several times. How do I produce a consolidated report with repetitive line items rolled up to one line item entry? So the total number of part number 1234 showing as one line item instead of six, the total of part number 5678 as a second line item, etc. I can create sub totals but I cannot find a way to consolidate to just one row for the total of each part number. Have you tried using pivot table? Sounds like a good fit. Check http://peltiertech.com/Excel/Pivots/pivotstart.htm for how to info....

Change Name of a Mutual Fund PERMANENTLY!!!!
After doing an update of my accounts, M05 questions me as to whether "Fid Contrafund" is a new fund or the same as one that I already own. Each time I tell M05 that the fund is the same as one that is already in my investment portfolio. I then select "Fidelity Contrafund (FCNTX)" from a drop-down list of investments. M05 then asks me if I'm sure they are the same fund and I reply Yes. The problem is that I have to go through this little trial EACH TIME I update. Does anyone know of a way to tell the software the answer to this question once and for all???? ...

Counting Different Names in A Column... Formula?
Just wanted to know if there is a Formula that can count DIFFEREN names in a Column? eg. There may be more than 1 same name, but I dont want it counted twice: James Edward Max John David James James Lisa Total should = 6 (Since James is 1 person) Is there a formula to solve this type of count? Thanks for any hel on this. -- Mh ----------------------------------------------------------------------- Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3598 View this thread: http://www.excelforum.com/showthread.php?threadid=57109 Try this For a list of...