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

Similar Articles

[PageSpeed] 12

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:

compare
Hello everyone, I have been reading this group for a while, however I am quite a rookie in using of excel. I would appreciate if someone could help with this issue. I quite often have to compare two sheets (from different workbooks-files, but with the same sheet name). Calculations are thus updated time by time and I need to check where were the main differences. Cells contain both values and formulas. I have found through this newsgroup nice add-ins of Myrna Larson and Bill Manville, and Rob Bruce. However I would need the macros to highlight only significant differences (let's say fro...

UnHide Columns in Excel 2002
In Office XP in Excel I can not select two columns it automatically expands the selection to include the Merged Row above it which includes all the columns below - not just the two I selected. When I select just two cells the right click does not include the option to "Unhide" or "Hide". I have to go to the format menu, select column and select "Unhide" or "Hide". Is there a way to select columns without including the merged rows within the spreadsheet? Is there a shortcut to "Hide" or "UnHide" with a right click or keyboar...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

Help on {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))}
Dear All, Plz help on this formula {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))}, how this works as I m new excel user. what does is meant $A$1:$A$7=$A$10 Hi As an array formula, it is testing each cell in the range A1 to A7 to see if it has the same value as that in cell A10 and returning what is the first row number that the value occurs in the range. -- Regards Roger Govier "Sandeep Jangra" <SandeepJangra@discussions.microsoft.com> wrote in message news:9A133D2D-75A6-4A3B-93AB-6680D164C647@microsoft.com... > Dear All, > Plz help on this formula > ...

Display nothing in a cell if value = 0
I have a range of cells formatted to display numbers in currency format. They have a formula applied to them so if the value of the Cell is nothing I get �0.00 showing. I would rather that nothing was shown in these instances. How is this achieved? Thanks Dom Couple of ways, use conditional formatting and display with a white font if the value is zero change the display option to suppress zeroes, Tools>Options>General and uncheck the Zero Values option. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mail...

2 workbooks
I have got 2 workbooks with a list of names. I need to find out if a name appears in both books. One of the workbooks came from a different source and the other one is a report I ran from our database. I need to find out whether the workbook from out of the company has got any of our own names on it. I am using Microsoft Excel 97 and am fairly new at this so please be gentle. One way I did think was to combine the two workbooks into one and find the duplicates but thought there may be another way. Thank you. Hi "queen on", Assume that In book1 the names are in column A, s...

need help referencing cell by formula result
I am trying to select a cell to reference using the result of a formula The spread sheet is an intrust calculator and I am wanting to bring th balance of the loan from say 6 months from =TODAY() to a cell. Th balance column shows the compounded intrust and subtracted payment month after month from the begining of the loan. I have a formula t find the coresponding row number for any month I wish to see th balance. I've tried =ADDRESS and some other things I'm not sure how t use. Any suggestions. Thank -- nosli ----------------------------------------------------------------------- no...

Comparing
I have two columns of numbers column A could be up to 3500 rows. Column B may be more or less than 3500 rows. What I'm trying to do is see if any number in columns "B" appears anywhere in Column "A", and if so we can just highlight it in both columns. Actually I would want to be able to sort by highlighted. so maybe add someway to sort by matched or unmatched. I hope this makes sense. In column C put... =IF(ISNA(VLOOKUP($A1,$B:$B,1,FALSE)),"Not in B","In B") In column D put... =IF(ISNA(VLOOKUP($B1,$A:$A,1,FALSE)),"Not in ...

Referencing two (or more) cell values in formula
Hi, Can anyone tell me the correct syntax for referencing two cells as a criterion in a formula. For example if I want to sum cells in b1:b5 if cells a1:a5 are greater than the value in c2 I would write the following: =sumif(a1:a5,">"&c2,b1:b5) If I want to sum b1:b5 if cells in a1:a5 are greater than the value in c2 and less than c3 what should I write? =sumif(a1:a5,and(">"&c2,"<"&c3),b1:b5) this doesn't work and neither does this.... =sumif(a1:a5,and(>c2,<c3),b1:b5). Any help gratefully received. Thanks, Will willcull@...

I need to compare to columns and indicate the matches in another
am wanting to compare 2 columns for exact matching. If there are two matching items I want to be able to say "A match" in a chosen cell for all the ones that match. Column C will be retrived in an random order... so how would I write the formula for that????? Example: A B C 45time 11tune A match 11tune 89time 47doog 43jkjkj A match 123ABC 123ABC If possible include how to highlight the ones that make as another option. Thanks Try something like this: For a value list in B1:B5 and a ...

1-way data sync into CRM (v3)
How do I determine if I need to purchase Scribe (and training) or write a customized web application using web service calls? (Any classes on that?) How do I know which would be less expensive now and in the long run? I have no knowledge of Scribe at this time, and I am not familiar with the SDK / C#. Issue: I need a one-way synchronization of data from a SQL database app into CRM for Leads, Opportunities, and Contacts. That data needs to be in CRM only for management reporting purposes and will not be updated by users. This will be an ongoing daily update process. The project man...

replacing text in all cells with existing text plus something
Hi, I have a sheet with several rows and columns containing text. I would like to add similar text to all the cells after the existing text. Is there an easy way to do that? thanks ---------------- 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/office/commu...

Stacked and Column Chart
Hello, Using the data below I would like to create a single chart with a Column Stacked Chart for Revenues and a Column for Expenditures. Can u help me? Revenues FY03 FY04 FY05 Private/Local Govt. $1,500,928.00 $2,598,394.00 $7,030,199.00 State $821,388.00 $3,467,473.00 $20,413,581.00 Ship Funding $15,806,578.00 $19,233,564.00 $23,902,364.00 Flowthru (Federal) $10,036,098.00 $10,595,406.00 $13,369,788.00 Federal Government $68,276,270.00 $1,604,082.00 $63,199,215.00 Expenditures $132,1452,481.00 $128...

Does the latest Excel STILL fail to auto-resize merged cells?
Whereas: - Excel can (wonderfully) auto-resize a cell whose contents are wider than its width. - But auto-resize doesn't work on a cell that is merged with another. - At the risk of being obvious, a feature is a feature only if it works. And Excel's auto-resize doesn't work. That is, unless Microflot has fixed this bug since my version? *** Same situation in Excel 2007, but in both versions there's a workaround. If the merged cells are A1:D1, for example, use a remote cell, say Z1, which is as WIDE as columns A:D, and contains =A1. Both are marked as wrap text. When you...

Dates changed on Copy Cell
I am now using Excel 2007 and keep finding that the year in the date is changed when I copy from one cell to another in another spreadsheet. I start with 9/12/2008 17:00 (formated as a custom format) and it is copied in as 10/12/2012 17:00 (same format). Can anyone help? Thanks Jim > changed when I copy from one cell to another in another spreadsheet. I assume that you mean Workbook not Worksheet in the same Workbook. You have the second Workbook set to the 1904 date system. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinato...

VBA unter Office v. X
Hallo zusammen, ich versuche gerade ein VBA-projekt an MacOS X anzupassen, aber ich kann keine Dokumentation oder Beispiele f�r DECLARE unter MacOS X finden! Konkret m�chte ich die Rainbow Sentinel API auf diese Weise ansprechen, aber ich konnte bisher noch nicht einmal herausfinden, welche Libraries Word �berhaupt unterst�tzt, und wie die entry points zu deklarieren sind. Ich w�rde mich �ber jeden Tipp freuen. Viele Gr��e Sascha Leib -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Sascha Ren� Leib - KOPFteam GmbH - http://www.kopfteam.de/ "There is not, and will...

Advice on comparing data sets
Hi, Can anyone advise on how I can compare data on 1 sheet with dat compared on another. Example attached. I want to be able to show that whenever the UK is shown (can appea multiple times) on this sheet it checks on sheet2 and enter th corresponding band value in this case for the UK (show in cells B6, B8 will show band A SHEET1 Ref Country Band 1 UK 2 Germany 3 UK 4 France 5 Italy SHEET2 Country Band UK A GERMANY B ITALY B FRANCE D SWEDEN D DENMARK E As always thanks for your help. Simo +------------------------------------------------------------------- |Filename: ...

Compare and delete row
I need some help - I need to compare a cell, let's say A1, that wil contain a date. I need Excel to compare it to today's date, if it i over 60 days, I need it to go ahead and delete that row. If not, I nee it to leave alone. Thanks in advanc -- Message posted from http://www.ExcelForum.com Try this Sub test() If IsDate(Range("a1")) = True Then If Range("a1").Value > Date + 60 Then Range("a1").EntireRow.Delete End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "intheway >" <<intheway.176s7x@excelforum-nospam.c...

unable to enter "/" in cell as 1st character
Why am I unable to enter the forward slash character (as the 1st entry) in a cell? Instead, it appears to act like the ALT key, accessing the File menus. never mind...I stumbled onto it in the Tools...Options...Transition tab. ;) "RobMlr" wrote: > Why am I unable to enter the forward slash character (as the 1st entry) in a > cell? > Instead, it appears to act like the ALT key, accessing the File menus. > > Rob Under Tools > Options > Transition you have a setting that tells Excel that the / key = Excel Menu Use the ' key before the / key ' t...

Moving a symbol within a cell
On imported data I often have the minus sign for a number appear on the right side of the numbers instead of the left side, I need to move it to the left so the cell data is a number and not text. Also not all numbers in the sheet are negative numbers. Is there a way to move this symbol within a pertinent cell without affecting the rest of the data and not doing it manually cell by cell. Hi! Have a look at http://www.asap-utilities.com/ which has an excellent freely-downloadable Add-In. One of the utilities in it does what you are looking for. Alternatively, put =--("-"&...

Service Pack 1 Update problem...
I have Vista Home Premium 32-bit OS. I possibly want to upgrade to Windows 7 equivalent. Here's my dilemma: I installed Service Pack 1 via Windows website download manager, and after a fashion, it appeared to have installed and it rebooted my system. It clearly shows as having been installed according to Windows Update History. [Additionally, Service Pack 2 is not offered as a download, but the rest of my query, please.] HOWEVER, according to Microsoft, when I click START > right click COMPUTER, at the top of that resulting page and beneath my version of Vista it sh...

How do i keep cells with a formula returning "" from being plotted
I have a chart that referrences cells that contain an IF statement that returns "". Although the chart option 'do not plot blank cells' is marked, Excel graphs these cells as being 0. Hi, Instead of "" use NA() Cheers Andy TBB wrote: > I have a chart that referrences cells that contain an IF statement that > returns "". Although the chart option 'do not plot blank cells' is marked, > Excel graphs these cells as being 0. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info unfortunately "" will plot as a zero...

How do I wrap text in the subject column in the list of tasks
i seeing the list of taks in using the menu "Tasks" in outlook. However I am unable to wrap the text under the subject column that I miss the words. Even when printed in landscape the words are truncated. So how do i wrap the text in the view and print out, so that I can see all the text. You can't, you can either remove and/or resize some columns or select the entire list and paste it into Excel and modify it from there. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads,...

sell cvv + transfer + ship + dumps + track 1&2...all country !
Hello all !! I'm seller cvv from Viet Nam Contact me - Y!m: khaisell - Email : khaisell******.com.vn us (visa , master) = 2 $ us (amex , dis) = 4 $ us (full) = Hello all !! I'm seller cvv from Viet Nam Contact me: - Y!m: khaisell - Email : khaisell******.com.vn us (visa , master) = 2 $ us (amex , dis) = 4 $ us (full) = 20$ us card with DOB = 10$ ====== uk (visa , master) = 5 $ uk (amex , dis ) = 10 $ uk (with dob) = 20$ uk (full) = 40$ ====== EU Italia = 18$ Germany = Sweden = 18$ Spain = 18$ France = 18$ Japan = 20$ Denmark = 20$ Au = 10$ Ca = ...

comparing cell
i have a spreadsheet that contain a column of name (some the same and some not) and would like to place each name in a row acroos the top of the same spreadsheet. Smith Jones Brown Smith Smith Brown Smith Jones Jones Smith Brown Hi Stanley First insert a heading above your column of names. Then Data>Filter>Advanced Filter mark the source as your column of names. Choose copy to another location and choose a detination cell on the sheet. Select Unique values only. Having got a unique list of names, copy this list and Paste Special>Transpo...