Compare 1 cell to column of cells returning adjacent cells info?

I want to compare the value in one cell to another column of cells in another 
work sheet and once the matching value is found I wan to return a different 
cells value that is on the same line as the matching cell. Example Sheet 1 
value is 34 in B6, so I want it to search Sheet 3 colum J for the value 34 
which is found on row 351. When a match is found the value returned needs to 
be from colum O so this would be O351. Does anybody know how to setup this 
function? 
5
Utf
4/15/2010 6:21:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
2957 Views

Similar Articles

[PageSpeed] 3

You are describing the VLOOKUP() function

=VLOOKUP(B6,'Sheet 3'!$J$1:$0$500,6,0)



"Mr. Fine" wrote:

> I want to compare the value in one cell to another column of cells in another 
> work sheet and once the matching value is found I wan to return a different 
> cells value that is on the same line as the matching cell. Example Sheet 1 
> value is 34 in B6, so I want it to search Sheet 3 colum J for the value 34 
> which is found on row 351. When a match is found the value returned needs to 
> be from colum O so this would be O351. Does anybody know how to setup this 
> function? 
0
Utf
4/15/2010 6:36:02 PM
Reply:

Similar Artilces:

How do I dynamically retrieve the cell address of the last cell t.
I want to write to an excel file that already has data in it using matlab xlswrite function. I found that I can write to the excel file without overwriting information that is already there. So I was wondering if there is a way in excel to retrieve the address or index of the last cell containing data. Nancy There are several ways of doing this depending on the layout of your data. Say you have data in all the cells of A1:A10. No blank cells in that range. You can use: LastCellAddress = Range("A1").End(xlDown).Address(0,0) This will give the address of A10. If you...

Problem with Table Column Not Expanding
I've got text in a table column that extends beyond the visible edge of the column. It appears that Word 2007 is not actually extending the table as it should but stops right at the page break. On the Table tab of Table properties, make sure text wrapping is set to None. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "David C. Holley" <David.C.Holley> wrote in message news:%23IRfeJbgKHA.5564@TK2MSFTNGP06.phx.gbl... > I've got text in a table column that extends beyond the visible edge...

can i use filters to compare data instead of vlookup?
I have to compare membership lists to identify if customers belong to certain groups. Currently, I have been using Vlookup but I notice that there are too many nuisances. Could I use a filter to compare two membership lists and give me a more accurate answer? Maybe try setting up a new sheet with List1, and entering a new column indicating List1 in each row......then adding List 2 at the bottom and entering List2 in the new column for each row in that section............then do Data > Filter > AutoFilter and check the arrows at the tops of the columns to filter from there...........

converting 1.x database to 2.0 from DOS files
I saved the 1.x database on floppies. Now I need the database again and need to upgrade it. How do I copy the floppies with the 1.x database so that it will work with access 2.0 in an XP pro environment? ...

copy text to specific cell
In one cell I have a String And in another cell I have a reference to a cell. How do I put the String into the referenced cell? A Formula will return a value to the cell that holds the formula. You'd need some VBA to do what you want. dim myStrCell as range dim myAddrCell as range with activesheet set mystrcell = .range("a1") set myaddrcell = .range("b9") .range(myaddrcell.value).value = mystrcell.value end with --- This has no validation at all! If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritch...

/r/n (13-10) (carriage return
Hi, A few days go I fiund iut that the xmlserializer strips away /r/n out during deserialization (and just leave /n) ... I found out that you can resolve this problem using the deserialize overlaod that takes an xmltextreader instead of a stream .. However when it's time to Web Services you have no such a control over the deserialization phase AND I found out that WS strips out /r/n (an leave /n) ... I understand there are some XMl specs related to this .. however . what one should do to have a /r/n sequence into a string property of his own custom object ? I saw that writing /r/n...

Risk and Decision Analysis @Risk, Evolver, Decisiontools, Roy Kelly FloorTrader Tools 8.3, AmiBroker 5.0, MultiCharts 2.1, NinjaTrader 6, OwnData 2.6, VantagePoint 7.0.11.2, other ...
Risk and Decision Analysis @Risk, Evolver, Decisiontools, Roy Kelly FloorTrader Tools 8.3, AmiBroker 5.0, MultiCharts 2.1, NinjaTrader 6, OwnData 2.6, VantagePoint 7.0.11.2, other ... please send e-mail to : ola 'AT' mail 'DOT' gr , ola3 'AT' mailbox 'DOT' gr , ( please substitute 'AT' with '@' , and 'DOT' with '.' ) , ola@mail.gr, ola3@mailbox.gr, ======================================= RISK ANALYSIS with @RISK --- @RISK 4.5.7 for Excel, Compatible with Excel 2007, ( Standard, Porfessional, Industrial ) @RISK 4.5.6 Internat...

No more 1 yr free MSN Bill Pay?
Is the 1yr free MSN Bill Pay gone? Just tried signing up from Money 2007 and I only see the fee-based options. Thx. Zoli wrote: > Is the 1yr free MSN Bill Pay gone? Has been since Money 2005! Regards, JB Thanks, I skipped a few generations and just upgraded from 2004 to 2007. It's funny to see a "complimentary value pack worth $1,300" advertised when the most meaningful one of all is removed :-( johnbusc@hotmail.com wrote: > Zoli wrote: > > Is the 1yr free MSN Bill Pay gone? > > Has been since Money 2005! > > Regards, > JB ...

How do I create columns in a catalog merge?
How do I re-arrange the merge fields into columns instead of rows? (Top to bottom - 3 columns per page) Resize the "catalog merge area" until it says it will repeat 3 across. Resize the merge area vertically so it says Area will repeat 1 down. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "dcallaway" <dcallaway@discussions.microsoft.com> wrote in message news:56B87903-2B55-46F8-9A19-5089F9A1C2C4@microsoft.com... > How do I re-arrange the merge fields into columns instead of rows? (Top to > bottom...

Counting of cells with a specific color filled
Hello experts, I have an excel workhseet where different cells are filled with different colors. I need to count no of cells in a specific column with a specific colour filled. For example i need to count how many cells are filled with green color and how many with red etc. What formula i should use? Thanks in advance, Atiq use the COUNTIF() function, the criteria is whatever was used in conditional formatting to change the color fill of the cell. If conditional formatting was not used to color the cell, you can't do it. (unless you use Visual Basic programming) ...

I have pound marks in a cell rather than words/letters.
I can double click and get the words but when I move off the cell, it returns to pound marks, Stupid question, but did you increase the width of your column? "claudia riley" wrote: > I can double click and get the words but when I move off the cell, it returns > to pound marks, What you are actually seeing when you dbl+click is formulas that produce numerical results, but the cells have been formatted in some way (Currency, perhaps). The program is telling you that the columns are not wide enough to display the entire formatted content so it won't display anythin...

conditional formula for blank cells
I have a conditional cell color based on a formula to identify blank cells & populated cells. I want to now count the cells that meet the formula criteria. I get accurate counts with the populated cell formula count but because I have an isblank in my blank cell conditional formula I am getting a miscount. Is there any really good way to count colors based on conditional formulas especially where the condition is blank? You should have no trouble if use use COUNTBLANK. You should get a count of blank cells no matter how they are formatted -- Gary''s Student gsnu200705 &qu...

Newbie question / plug in a value for a cell based on a range for that value
Hey guys, New to Excel so sorry if this explanation isn't the clearest in terms of correct terminology. In one section of my spreadsheet I have a list of percentage values associated with numbers 1-20. For example #10 is listed with the percentage value of 38.4% in the cell next to it (O14 and P14). In another section of the spreadsheet I have a quick formula that I'd like to use these %'s based on what number (1-20) is entered. In my spreadsheet the % I want to use for this #10 is listed in P14 (38.4%). What can I plug into my formula so when I enter "10" is kno...

MISSING Image Tools 1.0 Type Library
I have a database which I created for a doctors office. The problem is that the database runs just fine on my laptop computer. Bet when I get it to my job and try to run the database at work on their computers, I get the error message that I have a broken reference to the Snapview.OCX version 1.0 Library. and on the same computers when I take a look at the references one of the choices checked says MISSING: Image Tools 1.0 Type Library. I just don't know what to do. "Duck" <charles.kendricks@charter.net> wrote in message news:1185219678.412042.194320@o61g2000hsh.goo...

set cell value and workbook Creation date
How can I set the value of a cell with the creation date of the workbook? Each workbook is beaing created from a custom template. TIA W William, Either through a macro or through a User-Defined-Function: Sub TryNow() ActiveCell.Value = ActiveWorkbook.BuiltinDocumentProperties("Creation Date").Value End Sub Use this like =CD() Function CD() As Variant CD = Application.Caller.Parent.Parent.BuiltinDocumentProperties("Creation Date").Value End Function HTH, Bernie MS Excel MVP "William Hamilton" <william@NOthevirtualSPAM.PLEASEco.THANKSnz> wrote in me...

Return to the last accessed cell in a multiple paged file at reent
I have an Excel file at work that I did not create, with multible tabs (pages). Hyperlinks from the main page take me to the sub-pages. Some of the sub-pages open to the last accessed cell on that page, some take me to the top of that page. What can I do to make all pages open to the last accessed cell? ...

Column Headings #5
I'm using Excel 2002 I've managed to get the column headings stuck on numbers, as well as the row headings, and I really want the column headings back as letters, so it's easier to differentiate. Can someone tell me where the option is, please? Tools>options>general uncheck R1C1 reference style -- Regards, Peo Sjoblom "Jonathan" <anonymous@discussions.microsoft.com> wrote in message news:041b01c3dd42$d1a90180$a001280a@phx.gbl... > I'm using Excel 2002 > > I've managed to get the column headings stuck on numbers, > as well as the row...

Autofill a range of cells based a a txt value in a separate cell
I have a speadsheet that has a function to display the day of the week based on the date. I want to color in all cells (several rows and columns) under this header to distinguish weekends. I will still need to input text into these cells on an occasional basis. I am new to Excel, and I am trying to automate what a predecessor took hours to do mannually. Thank you for your help in this matter C20driver --- Message posted from http://www.ExcelForum.com/ Hi have a look at 'Format - Conditional format'. >-----Original Message----- >I have a speadsheet that has a functi...

In Cell Editing
Hi there: Been using Excel for years but haven't asked this question until now... Could have saved myself a few days likely! Anyway, say I type BACON in a cell and press enter or tab - the focus moves to the cell below. If I arrow back up to the cell that has BACON in it, how do I start editing it without double clicking on it with the mouse? I don't want to delete BACON and start again, I want to edit BACON. After I'm done, I want to eat bacon. haha Thanks Press F2 -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in ...

Change the default cell size in Microsoft Excel?
I've been trying all morning to figure out how to change the default cell size in Microsoft Excel so that each cell is the size of four cells normally. Is this possible? I realize I can select four cells at a time and then merge them but I need page after page of cells this size and its not very efficient to do it this way. Thanks in advance, James Open an excel sheet and click on Format coloumn standard width then put in the width you need Thanks Simon -- simon ----------------------------------------------------------------------- simonc's Profile: http://www.excelforum.com...

Possible to access macro in 1 workbook from any open workbook?
I have a macro that I run by hitting Ctrl-M. It's in Workbook A. I'd like it to be able to be run from any open workbook. Is there a way to do this? If you add the macro to your personal.xls workbook, you can run it any time you have Excel open. Check out http://www.rondebruin.nl/personal.htm HTH, JP On Mar 18, 9:23=A0am, "Ex...@shoenfeltconsulting.com" <ex...@shoenfeltconsulting.com> wrote: > I have a macro that I run by hitting Ctrl-M. =A0It's in Workbook A. =A0I'd= > like it to be able to be run from any open workbook. =A0Is there a way > to...

best return from WCF service to other .NET consumers?
We have a web application today in Framework 3.5. The application consists of a Data Access Layer, Business Logic Layer and a Front Layer of ASP.NET webpages. In the DAL, a number of typed datasets are defined, and it is these typed datasets that are used as data transfer objects throughout the application. Now we want to expose functionality, from the application, to other .NET systems, through a WCF service. What should we let the WCF service expose to other systems? Here are the alternatives we have talked about: 1. Expose the DataSets we already have (simply return the typed datas...

Alter table Add column to merge publication
We are currently on SQL Server 2008 and I would like to add a column to a table that exists in our merge replication. Below is what I ran in our test environment Alter Table TableName Add ColumnNameTypeID uniqueidentifier Null The column was added, but the Default value or Binding was set to NewID() and the RowGuid property was set to Yes. I would like the Default value or Binding not set to anything and the RowGuid set to No. Is there a way to do this using the Alter Table command? The new column added is a FK to another table. I am thinking after the column is adde...

return last value>0
I want to have a cell at the bottom of list named "current value" that returns the last value in a column that is greater than 0. What function would I use? Thank you. Hi Shawn one way: try the following array formula (entered with CTRL+SHIFT+ENTER): =INDEX(A1:A999,MAX(IF(A1:A999>0,ROW(A1:A999),0))) note: this won't work if there are also text entries in your range! Frank shawn modersohn wrote: > I want to have a cell at the bottom of list named "current value" that > returns the last value in a column that is greater than 0. What > function would I ...

Problems with Columns
Attempting to change existing text to two columns but sections in wrong order when changed to columns. What could be causing this? ...