How to lookup when range contains multiple identical entries?

Here's a simple example of what I'm trying to do on a larger scale. I have a 
raw list of data as below. I rank them elsewhere and then I use a lookup 
function to find match the country to the value. The problem is that when I 
do a lookup on "10" it gives me Egypt every time because it is the first 
match that it finds. How do I get it to skip Egypt the next time "10" appears 
and to show UK, and then Russia, etc

Example:
China	25
Egypt	10
UK            10
Germany   80
Russia	10
Spain	20
Italy	 5

0
1/18/2006 5:11:14 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
529 Views

Similar Articles

[PageSpeed] 49

Reply:

Similar Artilces:

Columns Entry
How do I use VBA code to lookup the last entry in a column. Select row 65536 in the column of interest and then control-up. You can record this and then look at the VBA: Range("I65536").Select Selection.End(xlUp).Select --Bruce "aftamath" wrote: > How do I use VBA code to lookup the last entry in a column. ...

Countif in multiple worksheets
I have a workbook, with multiple (70) sheets , each with it's own name. Each sheet states in cell D2 "Yes", "No", or "Not sure". In a summary worksheet I would like to count the number of times "yes" has been stated in cell D2 of all worksheets. With COUNTF I can't get the range right. Is it possible to do this? I sure could use some help, thanx Rob You cannot use COUNTIF (or SUMIF) with a 3D arguments such as =COUNTIF(Sheet1:Sheet3!A20,"yes") So you need a user-defined function If the summary sheet is the first one on the t...

Issue with local named relative range
Thanks to the Excel wizards here, I got some great tips back in November with creating a way for a cell to refer to itself (e.g. THIS) or the cell above it in a formula. If the active cell is A2, I can define a name. e.g. CellAbove, that refers to A1 (no dollar signs = relative reference). This was a pleasant surprise for me, as I had not realized nor ever tried to create a named relative range. However, I've now come across an issue with this technique and I hope the wizards will assist once again. It seems that Excel (2003 at least) always places the sheet name in the refere...

Containers, documents, views and asserts
This is going to be a long explanation, so bare with me please. I have a basic application using Win32 that I am using to render various media files (images such as bmp, jpg, tga, etc) using DirectX. This application has a plugin architecture I have set up so I can add support for other media types by adding more dlls. Each plugin dll can add support for one or more media types. Recently I have attempted to add support for rendering flash files to my application (.swf files). I had previously written a test app using an SDI MFC setup. This prototype stole a lot of code from the test Acti...

VBA code for copying on a range were cells are blank
Hi everyone, me again!! So, you were all so helpful with my earlier prob, thought i'd keep you going with another prob i'm having. OK, so what I want to do (in VBA if poss), is to search the range B2:E2 and see if they are blank. If yes, then I want to copy the cells above and then carry on searching that range. I can't do it upwards as its likely that there are going to be more than one set of blanks, e,g. A B C D E F 1 Joe Bloggs | 309873 | Smith, John | 08:00 | 12:00 | Back Office 2 | | ...

managing multiple mailboxes from outlook
I have a user who is managing multiple mailboxes from within outlook 2000 via the folder list. She has been creating contacts in the second mailbox's contacts folder without problem and created a distribution list as well, however, when she goes to add the contacts of that mailbox to that DL she can only see her contacts and the GAL but not the second mailbox's contacts from the dropdown list. Any ideas? ...

synchronization multiple mailboxes
Hello, Is there a way to sync 2 mailboxes in Outlook 2002 or 2003? Hello Damon, Have a look at www.slipstick.com/outlook/sync.htm or at my signature for a powerful tool. -- Thomas Wetzel www.synchpst.com Synchronize and backup Outlook ...

How to have a date range equal a particular month
What is the expression for converting multiple dates mm/dd/yyyy to the coinsiding month? It is according to how you are going to use the data but try this -- Format([YourField], "yyyymm") This will give you 200711 for this month. Format([YourField], "mmmm yyyy") This will give you November 2007 for this month. -- KARL DEWEY Build a little - Test a little "tvillare" wrote: > What is the expression for converting multiple dates mm/dd/yyyy to the > coinsiding month? Format(DateField, "yyyy-mm") as stri...

Make calender entries in other timezone?
Hi All, I'm based in Australia and work for a company based in another country. I work to their day, and often ring in for meeting etc. I'd like to know if it's possible with Outlook's calendar to make appointments using another timezone that automatically translate to my timezone? As it is at the moment, I have to figure out when the meeting falls in my timezone and book the appointment that way. I've worked out how to display the other timezone in my calendar, but I was wondering if there's a way to schedule appointments more intuitively? IE, a way to make the appo...

Why does my mouse not release a range of cells when selected?
When I try to select a range of cells, the mouse will continue to highlight a range of cells and will not release. I end up having to force Excel to close and restart. Any help would be greatly appreciated. It does it with most versions of Excel. Hello Check if the F8 key was not pressed (you would have the "EXT" mention on the Excel Status bar), if yes press F8 again. Apart from that I think this could be a mouse driver problem so to make sure try and update your mouse driver (esp if you have a wheel mouse). Apart from that? HTH Cordially Pascal "Wolfer50" <Wolfer...

Graphing Multiple axes
I want to graph 5 curves on one graph. I need to plot data and use 2 y axis and 3 x axis. Hi, You will need to create dummy axes yourself. See Jon Peltier's examples for the techniques needed to build you own axes. http://peltiertech.com/Excel/Charts/TertiaryAxis.html http://peltiertech.com/Excel/Charts/StackedLine.html Cheers Andy Heather wrote: > I want to graph 5 curves on one graph. I need to plot data and use 2 y axis > and 3 x axis. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Please vote for multiple axes in Excel for that enhancement. Thanks, Jim...

Count cells in a column that contain dates
Column J contains dates in some cells, and text in others. I need to count the number of cells in this one column that contains dates. How can I do this? If the only cells that are in that column that are numeric (like dates), you can use: =count(a:a) (Dates are just numbers formatted nicely in excel.) Cachod1 wrote: > > Column J contains dates in some cells, and text in others. I need to count > the number of cells in this one column that contains dates. How can I do > this? -- Dave Peterson ...

Multiple user accounts not working properly
I am running Windows XP and have three user account logins. I was also running Outlook Express and set up three email accounts (each on their own login).. the first two email setups worked great. But the third one wasnt recognizing my password as valid. I Contacted the ISP. Walked through setup. Still didnt work. ISP instructed me to access mail via the internet. That worked. ISP said that proves the account is valid. Now, I just upgraded to Outlook 2003. Same problem with the third email user account. Funny thing is that this "3rd" email account is one of the tw...

Copying "To-Do List" entries to Word or Excel
I track all of my "things to do" on Outlook 2007 Tasks manager. I want to be able to print "to do lists" for specific categories and be able to transfer all of "to do" entries under a specific category, ie, Monthly Team Meeting, into a Word document to edit for agendas. How can I export my list of "things to do" from Tasks to be used hard copy or in Word? -- Tom Copy and paste... see http://www.slipstick.com/Tutorials/copyto/excel.htm -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook &...

Duplicating data to multiple pages of the same wookbook
Can I enter data on pg1 of a wooksheet & have SOME of it automatically enter on pg2? Hi Depending on what you're really after - the following in a cell in Sheet 2 will put the data from cell A1 in sheet 1 into it. =Sheet1!A1 Cheers JulieD "DarrellK" <DarrellK@discussions.microsoft.com> wrote in message news:BD21312C-ECD8-4D80-B4E3-6C8AC9A095C1@microsoft.com... > Can I enter data on pg1 of a wooksheet & have SOME of it automatically enter > on pg2? Hey there, This is useful for me, but if I have the name of a sheet in a cell could I use that somehow in...

OL2003 Multiple message save drops body of message
When a group of e-mail messages are selected & the FILE- SAVE command is run with the intent of creating a file containing those selected messages --- only the header info from the messages is included in the file created. The body text of all the messages is lost/discarded during the save process. Is there a way to save multiple messages to one file and not loose the bodies of the messages? Previous versions could do this. Hi I had the same problem. You need to contact MS and refer the tech to KB838020. This is an internal (to MS) article not available to the public yet. They sh...

how to chart same cell in multiple sheets
I have an excel template with 3 worksheets that I created for others to input information into certain cells on each sheet on a daily basis, and resulting save with similar file names. I want to compare the information between workbooks (plotting) to see if the trend results are going up or down. Is there a way to plot a chart, based upon the information in one specific cell (or same specific group of cells) within multiple sheets (excel 2000 or 97)? I can present the information in various ways. 1) Same cell or group of cells within same named worksheet, over many workbooks (files)....

VBA
Dear all, I have several charts in a sheet for which I need to change the data ranges. Currently each chart has a different data range, and for each chart the new desired data range is just an straightforward extension of the original (i.e., the new data ranges could be easily calculated from the original ones just by adding a fixed number of rows/ columns). I am therefore trying to implement a macro for updating the data ranges of my charts automatically, but so far I haven't found a way for getting the actual data range of a chart. I guess I'm looking for something like the opposit...

Lookups and References in relations to countA and sum products....
I have 2 sheets, one summary, and one detail. The detail is as follows: Dept units 331 12 331 24 331 331 12 332 332 36 332 24 333 The summary is as follows: Dept # of styles 331 3 332 2 333 0 I want the formula on the summary sheet to count the number of non blank entries for each dept. Which formula is it? -- JR573PUTT ------------------------------------------------------------------------ JR573PUTT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31587 View this thread: http://ww...

Reprint Invoices in batches according to ageing range i.e.. 16-30
Hello, My Company is working on a suggestion that a collection company recommened and that is to send the aged invoices out in groups on a weekly basis reminding the customer that they are late. They suggest printing in buckets of 16-30 days, 31-45 days, 46-60 days, 61-90 days and over 90 days. That is 5 different batches that they recommend to go out each week. I have recommend using the dunning letters that is attached in the RM Customer Card section. But that is not what they are looking for. How can I reprint the invoices in the batches that I stated above? We have been print...

Outlook & Multiple SMTP
I'm trying to set up outlook 2000 to allow the user to select between different SMTP addresses when sending a mail. In 2002, I have it set so that when I write a new message, I can choose the account that's sending (and if I don't choose it uses the default account.) I have a friend using Outlook 2000 and she has 3-4 email accounts set up, but she has no choice in which account the outgoing mail is sent from. Does it have anything to do with Internet Only vs. Corporate installation? Thanks Yes - you want IMO mode for multiple smtp setup. "Jane" <noel_x...

Creating Multiple charts in an Excel Sheet :Post 2
Hi, I am posting this for the second time.Please provide me some help. I need to create a macro in Access which creates a report in an excel sheet. The excel sheet should contain a number of sheets (Sheet 1,Sheet 2,Sheet 3....).Inside each Sheet around 9 charts have to be displayed. Is it possible to create a macro for the same? Please can somebody help me with some vba code for creating multiple charts in a single excel sheet. Thanks, Sobha You should be able to find something here for the charting piece. http://www.peltiertech.com/Excel/Charts/chartvba.html "i.sobha@gmail.co...

entries
why doesn't some of my checkbook entries not appear in my money entry even after doing a live update? I'm not concerned with the amount....the whole entry is not coming up. Money reflects whatever your bank puts in the download. -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny UK Wishes/Suggestions mnyukwsh@microsoft.com "Judy" <anonymous@discussions.microsoft.com> wrote in message news:1353801c3c1a2$ecd39da0$a601280a@phx.gbl... > why doesn't some of my checkbook entries not ap...

Restrict entry types
Hi, I have (rather stupidly) put myself up to whip up a spreadsheet for work which is due to be unveiled at a meeting this morning. there are a few things which i'm struggling on, although i'm certain they are relativley simply to implement. 1. I need to restrict the date entry so that it views with /slahes as apposed to anything else. So even if someone inputs 29.11.05 it will automatically default to 29/11/2005 2. Similar problem with the time i need the time to default to 12:00 as opposed to 12.00 or any other value This is because they are going to be exported into a database a...

Simplify SumIF with multiple sheets
I have looked on the internet for a solution to simplify the following formula =SUMIF(Clin1!E34,$A$1,Clin1!$W34:W35)+SUMIF(Clin2!E34,$A$1,Clin2!$W34:W35)+SUMIF(Clin3!E34,$A$1,Clin3!$W34:W35)+SUMIF(Clin4!E34,$A$1,Clin4!$W34:W35)+SUMIF(Clin5!E34,$A$1,Clin5!$W34:W35)+SUMIF(Clin6!E34,$A$1,Clin6!$W34:W35)+SUMIF(Clin7!E34,$A$1,Clin7!$W34:W35)+SUMIF(Clin8!E34,$A$1,Clin8!$W34:W35)+SUMIF(Clin9!E34,$A$1,Clin9!$W34:W35)+SUMIF(Clin10!E34,$A$1,Clin10!$W34:W35)+SUMIF(Clin11!E34,$A$1,Clin11!$W34:W35)+SUMIF(Clin12!E34,$A$1,Clin12!$W34:W35)+SUMIF(Clin13!E34,$A$1,Clin13!$W34:W35)+SUMIF(Clin14!E34,$A$1,Clin14!...