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: Excelquestion.zip                                        
|Download: http://www.excelforum.com/attachment.php?postid=5006     
+-------------------------------------------------------------------

--
spgprivat
-----------------------------------------------------------------------
spgprivate's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3586
View this thread: http://www.excelforum.com/showthread.php?threadid=56016

0
7/11/2006 11:06:59 AM
excel 39879 articles. 2 followers. Follow

1 Replies
709 Views

Similar Articles

[PageSpeed] 12

Use the VLOOKUP function as such:

=VLOOKUP(B6,Sheet2!$B$6:$C$11,2,0)

copied down as needed

HT

--
swatsp0

-----------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1510
View this thread: http://www.excelforum.com/showthread.php?threadid=56016

0
7/11/2006 11:44:39 AM
Reply:

Similar Artilces:

Outlook 2007
I upgraded from Outlook 2003 to 2007 yesterday on my home pc - connected to the net via a cable modem. With 2003, when I closed Outlook, nothing would send and receive so I could check mail during the day via the web (at work) or on my phone. With 2007, even with Outlook closed, it's still sending and receiving email. I did see in the help file that you can set it to send/receive every 1440 minutes - but when I'm travelling, I never want it to connect at home - until I get home and tell it to. How can I set Outlook NEVER to send/receive when it's closed? Thanks, Susan susankr...

data range props
I have a problem with external data. In the Data Range Properties, under Data Layout. There are 3 options after 'If the number of rows in the data range changes upon refresh:' 1 - Insert cells for new data, delete unused cells 2 - Insert entire rows for new data, clear unused cells 3 - Overwrite existing cells with new data, clear unused cells. I want option 3 to be the default oprion but it always defaults back to option 2. Is there any way I can change this.?? Cheers Baz ...

Advice
I am going to start a new job in a month looking after an Exchange 2000 server and would like to know if there are any good resources that anyone knows of online for a complete newcomer to all things Exchange as I need to get up to speed pretty sharpish! Most of the sites I've found seem to assume a degree of knowledge and I have none! Any help would be greatly appreciated. TIA J My advise is to go an visit your local book store and see what they have fir Exchange 2000, there are some excellent books out there, not sure what knowledge you have with regard to Active Directory but you ...

Import data from Access to SQL
Hello everyone. I have an instance of SQL Server 2008 Express on my PC, I have need to export tables from Access 2007 to SQL. I did successfully migrate (table access with unique ID field primary key) with SQL Server Migration Assistant for Access 2008, I rechecking the tables in SQL and it's all ok. Linking tables in Access. I have saved import (from text files) that work without problems with Access tables, when I go to do one of these imports, at some point the message "Unable to register changes. The entered value violates the settings for the table or the list...

refer to data on multiple worksheets using hlookup/look up data on many worksheets?
Hi I have data on many worksheets in the same workbook. Is it possible to look up data on multiple worksheets using Hlookup, which means can the second argument in the formula refer to multiple sources? If it is not possible, is there any other way I can do so? I would like to select and display data according to a fixed order, whereby the data is located in one of many worksheets in the same workbook. For eg, I have stock returns of many firms in many worksheets. Say I would like to display in a single worksheet the returns of Firm D, Firm Z, Firm R; whereby the data of these 3 fir...

How do I validate data using different lists based on the data in.
How do I validate data using different lists based on the data in another cell? There are instructions here for dependent data validation lists: http://www.contextures.com/xlDataVal02.html Shannon wrote: > How do I validate data using different lists based on the data in another cell? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

can I make a bell curve from this data?
A colleague at work asked if I could put some simple Excel data into a bell curve. I don't know what I'm doing, math-wise, and I don't know if my data even lends itself to a bell curve. Here it is: We have 19 cars in our division. There are usually 3 cars that are driven a lot and 3 that don't get driven much, so we periodically rotate the highs and lows so that mileage somewhat evens out. So what I have is the annual mileage for each car. My colleague is hoping this will look great on a graph. Can this be put into a bell curve format? Or would some faux work-around make it l...

how do i arrange survey data best for a pivot table?
There are pivot table instructions and links on Jon Peltier's web site: http://www.peltiertech.com/Excel/Pivots/pivotstart.htm Mormonchick wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

OfficeQuestions looking for feedback and advice from Outlook users
Hi all, I am a long-time Outlook user and have used resources like Google and newsgroups for years to help find answers to my questions about many MS Office applications. My biggest frustration with using these types of resources was that it is cumbersome to sift through search engine results to find the answer I needed, and newsgroup threads make it difficult to pick out the best answer among all of the responses. So, I started a new site that I hope will serve the MS Office community by providing an inviting platform for asking and answering questions about MS Access, Excel, InfoPath and ...

Deleting parts of data
On my data sheet, I have 5#-4#-2#(on colume B only Ex 12345-6789-01) was hoping to find a way to delete hypens and last 2#. It would tak me a while to erase this manually. thanks for your help! -- Message posted from http://www.ExcelForum.com Hi if this is stored as 'Text (and is not a custom format) try the following formula =SUBSTITUTE(LEFT(A1,LEN(A1)-2),"-","") and if you want this stored as a number use =--SUBSTITUTE(LEFT(A1,LEN(A1)-2),"-","") -- Regards Frank Kabel Frankfurt, Germany > On my data sheet, I have 5#-4#-2#(on colume B ...

installation advice
I will son be moving from XP and Outlook Express to Windows 7 and therefore maybe to Windows Live Mail or Thunderbird. I therefore need some advice. Being paranoid, I prefer not to participate in cloud computing and would like to have all my mail on my PC and my ISP's mail server. I have POP3 accounts. Is there a way to a) install WLMail without its components (like calendar, etc.) which may reside on MS servers? b) have it place my storage folders on my E: "data partition" and not in the default C: (documents and settings) drive location? Thank you. Jeff ...

Data Validation #15
Hi all, a really picky question, why does the data validation allow a space after the list value? for instance, I have data validation to allow "yes" or "no" and I have some other sumproducts looking to these cells to give me figures, but it will allow you to put "yes " or "no " which the sumproduct wont pick up with the blank space in. can this be prevented? and why does it allow the blank space? Duncan I am trying to post a new topic but it keeps replying to this thread instead!. sorry, ill try again Duncan ...

clearing data in a cell but keeping the formula
I am wanting to erase the data from all fields, but keep the formualas in the cells. How do I do that! Thanks. 'Clear User Lists on Control Sheet Sheets("Control").Select Range("A8:E65536").Select Selection.ClearContents Range("A5").Select Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1)) Access doesn't have cells. Therefore I think that you are in the wrong discussion group. Try Excel. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder....

Formula/Function for parsing data
I have a column of data which is not consistent. Examples of the data in the column: C- Name, CO- Name, CN- Name, Name, etc. I want to write a formula/function that will automatically return only the name (first and last). I know I can convert my data from text to columns or even write a macro to do this but I don't want anyone to have to remember to do anything. I want the name to be returned once the data is pasted into a template. Thanks. How complete are your examples; that is, when the name is not by itself, is the additional text **always** in front of the na...

summing data that match critieria in a rnage
I cannot figure out what function to use to do the following: For example, if I have data like this: Criteria Sale $ # Sales <500 50 3 501 -999 39 1 1000-2499 1000 4 2500+ 1200 1 1550 3 I would like to know how to sum all the data in the # sales column according to the criteria based on Sale $. In other words, what is the total # of sales that were made < $500, between 501 and 999, b...

Ex2k3 - resource rooms - autoaccept agent
Hi, We use the autoaccept agent to manage most of our meeting rooms, the problem is we have a room that holds say 15 people, and users are booking 1to1 meeting (2 people!). Is it possible to write an eventsink to check these on delivery for the number of invitees? Thanks Matthew Yes you should be able to do this if you use a synchronous event sink and you adjust the priority on the existing Autoaceept registration so that the your sink is notified first before the autoaccept sink. To get the number of attendees on a message request you should be able to loop through the IAttendee ...

Re: Compare chart
Hi, I need to compare two products that are described by a set of standard attributes each. If all the attributes were same range of values that would be extremel easy task - you take a radial or line chart and consider it done. The trouble is each attribute has completely different range of values One of them is 0-1, often a few percents, while other ranges 0-10 000 As a result if I put it in the same serie and use the same scale i will not work properly. Moreover there is no correlation between th attributes, so one cannot forecast the scale limits. Any ideas how to show this? Thanks, ...

Setting custom chart causes plot area to collapse
Hi, When i make a chart template and set it as a user-defined default chart, and press F11 to apply it to a selection, the chart appears with the plot area collapsed to a thin line along the axis. When i re-start excel, this also applies to other charts, not only to the one made by F11 - even in the preview of the chart wizard, charts appear as a thin horizontal line. The only way i can make charts then is to copy a previous one and change its data - very time consuming! Do you know what could be the problem, and how to fix it? thanks! ...

inverting data #2
Please help, I'm stuck! I conducted a survey where respondants each gave answers to 30 questions using a Likert scale i.e. they answered either 1,2,3,4 or 5 to each question. The data is in the following form: Question # q1 q2 q3 q4 q5 1 2 4 2 1 2 2 4 1 2 1 4 3 4 4 2 1 4 4 2 3 2 2 5 5 2 3 4 5 3 My problem is that half of the questions were asked in a negative direction to avoid possible response bias. For the answers to these questions, I need to reverse the answers. If the repondant answered 1, I want to record 5, if 2 then 4, if 3 then 3, if 4 then 2, if 5 then 1. Is there an...

Setting account specific environment variables
Is it possible to set an enviroment variable for a Standard User Account? If so, how is that done? Do you use the System Control Panel? Thanks for your help, Chris On Sun, 22 Aug 2010 11:56:23 -0500, Chris wrote: > Is it possible to set an enviroment variable for a Standard User > Account? If so, how is that done? Do you use the System Control > Panel? > > Thanks for your help, > > Chris If you mean "can a standard user set an environment variable", I don't know for sure if you can, but I would think you could. CP -> System a...

data disappearing
Dynamics GP v9.0 user has new computer Problem: User Defined information and line items 'disappear' upon reopening an open ORDER. We have ONE user out of 22 who has this problem. She is our main order entry person. She will enter orders and a few days later, go back and open an order to add information to it and all line items and information has disappeared. this is only happening on her computer - this is the first time it has happened since getting her a new computer and loading everything up. PLEASE - IF ANYONE knows of any reason why information would disappear and on...

Comparing now()
This is a multi-part message in MIME format. ------=_NextPart_000_002A_01C85E16.D97BF490 Content-Type: text/plain; charset="big5" Content-Transfer-Encoding: quoted-printable Hi, I have a price quote with a timestamp which is returned from the = server. The timestamp is in text with format 01/23/2008 16:01:00.=20 Can anybody help me to compare the timestamp with now() so that the = closing price can be identified? ------=_NextPart_000_002A_01C85E16.D97BF490 Content-Type: text/html; charset="big5" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC &q...

set default focus to nothing?
In a pop-up form with several fields, some users are typing in data to the field with the default focus, before they look to see what field they should actually be typing in. The result is incorrectly changed data. I would like to force the users to choose a field before they start typing. But it seems the default focus is always going somewhere (1st item on tab control). Apparently I can't set it to a label. The best I've been able to do is create a dummy text box & set the default focus there. But there must be a more graceful solution? Thanks in advance. I ...

Upgrading Advice
For many years, I have been using Publisher 97 with Windows 95, without problem. Unfortunately, the time has come to upgrade, which at my age (and the fact that I am basically a Luddite), will involve a lot more than just a steep learning curve. I will be upgrading to Windows XP and Publisher 2003. If anybody can remember Publisher 97, do they think I will have many poblems integrating my Publisher 97 files? Also, I have a website created in Publisher at http://www.songwriter.co.uk/ and exported to the web using the Publisher Web Wizard. The only (minor) problem I have had with this was th...

Customizing report in MS2005 causes data to vanish
While customizing a Report by Category, I clicked on the Details and Amount tabs and all my data vanished in the report. What'smore I cannot seem to restore the data in the report...? Bug? Or is there a workaround? Thanks. BTW: A feature(s) request: It would be great to be able to get category data grouped quarterly (not just monthly) It would be great to see a horizontal report with categories displayed monthly and quarterly (listed across the top) Finally, sorting a report by perhaps two or three criteria would be great (as I see it, it is only one creterion currently) Thanks ...