#### Problem with vlookup

```Hi,

Column A1:A10 has the numbers 10-1 and B1:B10 has price of a stock.

Problem when I lookup the number = 12, it returns always the value in cell
A10.

Vlookup(12, A1:B10,2)=Value in cell B10,

But A10 = 1, and this is not close, I want the closest number which would be
10, the value in cell A1.

Is there any way to do this?

Thanks for your help
```
 0
Jeff1 (635)
11/26/2004 4:11:06 PM
excel.misc 78881 articles. 5 followers.

2 Replies
351 Views

Similar Articles

[PageSpeed] 4

```type
=vlookup(12,a1:B10,2,false)
it will give #N/A
becaue there is no number  as 12 in column A.

Jeff <Jeff@discussions.microsoft.com> wrote in message
news:FE1D3FBA-1BC1-47E9-8AE4-5C7015282E52@microsoft.com...
> Hi,
>
> Column A1:A10 has the numbers 10-1 and B1:B10 has price of a stock.
>
> Problem when I lookup the number = 12, it returns always the value in cell
> A10.
>
> Vlookup(12, A1:B10,2)=Value in cell B10,
>
> But A10 = 1, and this is not close, I want the closest number which would
be
> 10, the value in cell A1.
>
> Is there any way to do this?
>
> Thanks for your help

```
 0
R
11/26/2004 4:56:58 PM
```=vlookup(match(12,a1:a10,1),a1:b10,2,false

--
dcronj
-----------------------------------------------------------------------
dcronje's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=528

```
 0
11/26/2004 5:29:17 PM

Similar Artilces:

Text colour printing problem
Hi, We are having problems printing a spreadsheet from a workmate computer. Most of the data is black with the exception of a of a fe rows where the text is red. when the user tries to print the documen comes out in greyscale, I have checked all settings and confiqs an still it prints greyscale, I even opened a new worksheet and typed ou a red test test page, and this prints correctly. I compared th settings of the two sheets; my test one and the orignal sheet and ther was no noticable difference. The machine is running Win 2K Pro wit Excel 2000. I hope someone can please shed some light on t...

Outlook Email Merge Problems
I have no problem sending email merges at work with our ISP. However at home, using my ISP, Comcast, I am having problems. I sent an email merge using Outlook 2007 and Word 2007. The merge blended perfectly, no error messages and shows as being sent in my sent folder. However, none of the emails are received by the recipients. I sent 4 test emails to myself and 3 other accounts and no one received any of the sent emails. My deduction is that if it's merging correctly and showing in my sent folder then the problem is with my ISP however they say they have no problems. Anyone have any sugge...

Ever since the update last week i'm having issues downloading my account from wells fargo. i have set money up to download transactions again.... But...... Prior to the update, money would only download transactions that have cleared, not ones that are still listed as 'pending'. i was able to then list the pendings in my account register and this would show me an accurate available balance. Now, money is downloading pending transactions as soon as they arrive, but if a charge is listed as pending for multiple days(which happens a lot) money will download this multiple tim...

bitmap problem #2
Hi all, I am new to MFC programming can some body explain how to use a bitmap (.BMP file) in a dialog box as a static control. I got little piece of information from a broken link that, The bitmap is defined in the .RC file as BITMAP_NAME BITMAP DISCARDABLE "filename.bmp" and used in dialog as CONTROL "BITMAP_NAME",IDC_STATIC,"Static",SS_BITMAP,12,11,21,20. Can some body explain steps to add a BMP (to dialog box as static control) to a MFC skeleton Thanks in Advance, SeemaRao. Just attach bitmap hanbdle to Control Variable CStatic::SetBitmap() You can d...

bsearch problem after re-sorting
Hello! I have the problem that after re-sorting my array with qsort, bsearch does not work correctly. Here's what I do: - I create an array and fill it with elements - I sort the array with qsort - If I try to find any element with bsearch, it works fine - I add a new element to the array and re-sort again with qsort - If I try to find this element with bsearch, it returns NULL? I checked the contents of the array and the element was really added and re-sorting also worked correctly. Additionally, I found out, bsearch works correctly, if qsort does not put the new element to the array...

Tricky little combo/table problem
Here's a tricky little problem I am trying to figure out. I'm sure someone out there should be able to help me without breaking anything. Heres the problem.... I have a form that has an underlying table attached. I have a combo box pull in a pull down list of locations from that table. What I would like to happen is, I would like it so the user will select a location from that combo box, and then click a command button. When they click that button, I need whatever they chose in the comobo box to copy over to a NEW table, which will be referenced later by my program. Say the table being...

Callout problem
I=B4m programing a postupdate callout over one Custom entity. I write the callout in VS 2003 and I put the callout dll and Callout.config.xml in the right path of the CRM server, but when i tried to probe, the callout not fired. Anybody knows what I forget it. Thanks Hi , There is a hotfix that may possibly help. http://support.microsoft.com/kb/913549 Have you done any precallouts? Do they work ok? You need to do an iisreset (restart the WWW service). CRM reads the configuration file on startup, but it does not check for updates. If it still doesn't fire, please post your code and t...

Problems converting old DB to Access 2007
I have a DB created in a prior version of Access (I think it was Access 2003, fiel extension ****.mdb) that I am trying to convert to Access 2007. The help instructions say to open the DB, do a "save as" Access 2007 format. However, I get error message saying "You attempted to open a database that is already opened exclusively by user 'Admin" on machine 'xxxx'. Try again when the database is available." I am the only one who has the DB open. The machine referenced in the error message is my machine. What is going on and how do I fix it...

MSDN library problems
Hello, I have had to reinstall my Development Studio 97 software. However, because I had previously installed version 6 and Net.2003 versions my old MSDN library cannot be found by Studio 97 (although it seems to install all right) I suspect this is a registry conflict problem but I would welcome any solutions. I am on XP-Professional Many thanks, newsgroupie ...

Access 2000 / Word 2003 Merge Problems
I've designed and worked with a database in Access 2000, and one ofthe end users (who has Office 2003 on their machine) has to usequeries I setup for him in Word 2003 for a mail merge. Lately, any newqueries and also and existing queries I make changes to will not showup when he attempts to perform a mail merge. I've scoured thenewsgroups and the Microsoft KB to no avail. Any thoughts?- Jim...

Problem with printing multiple copies of a report
I read and follow the suggestion from this group to wrtie the code for a print sub: DoCmd.RunCommand acCmdSaveRecord DoCmd.SelectObject acReport, "rptCertificate", True DoCmd.PrintOut acPrintAll, , , , InputBox("Enter Number of Copies", _ "Print repCertificate", 1), 0 Access prints one copy even I put 2 in the InputBox or use DoCmd.PrintOut acPrintAll, , , , 2, 0 in stead. Thank you in advance for your help. -- Jeff ...

Printing problem.
Hi, i'm having a strange problem with some excel files in a Windows 2000 Server (no service pack applied) network. Some users are able to print a file while other are not. No error message is exhibited on screen. Users have the some network rights. This happens only with a few files, all of them located on the same folder on the server. Clients are using Windows 98 SE and Windows Me. We are using Office 2000 SR1 Thanks, -- Carlos Vaz --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.510 / Virus Database: 307 - Release ...

Backup Problem
Dear fellows I am having this kind of problem when i am trying to take a bakup from the server and in the event log this error is coming. BACKUP failed to complete the command BACKUP DATABASE [NSCL] TO TAPE = N'\\.\Tape0' WITH NOINIT , NOUNLOAD , NAME = N'NSCL backup', NOSKIP , STATS = 10, NOFORMAT So Please i need help and i need it very fast. Please can any one help me i really and really stuck please -------------------- "10mbps" wrote: > Dear fellows > > I am having this kind of problem when i am trying to take a bakup from the > server...

UMDF CreateWdfFile problem
Hi, I used CreateWdfFile() in my UMDF driver in order to create a file object for my driver created request. However, when I call Close() for that file object in my OnReleaseHardware(), I always get the following exception in Windbg: **** WUDF DriverStop - Internal error 0x400000000000000 **** in Host **** d:\w7rtm\drivers\wdf\umdf\DriverHost\common\inc\private\TrackedObject.h:347(CTrackedObject::Release): Dereference of destroyed object, Context Record: .cxr 0x000000000100EC40 I ended up commented out all the code and simple call CreateWdfFile() in my device Configure(...

ctrl+end problem
ctrl+end no longer takes me to the end of the worksheet - only to the end of the current row. Ctrl+home is not working either - only to the beginning of the row. This is only in Excel. The combinations work properly in Word and other applications. ...

Clip Art problem
I loaded Publisher 2002 on an XP Pc about 3 weeks ago & it worked fine. Now, all of a sudden, when you search for clip art it either doesn't find anything or finds a very limited amount when for example for states it used to find all 50 states & now it finds a globe. I have tried to fix errors, uninstall & reinstall to no avail. Can anybody help me with this? Thx, R.Fritz ...

Problems after company adopts Exchange 2007
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3345872277_60745520 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit Hi I use entourage 2008 to connect via the exchange server to a company that I am involved with. Recently I was informed that they were moving my mailbox to Microsoft Exchange 2007. Since then I have not been able to connect from Entourage, I get a message "Entourage cannot connect to the Exchange Server" "er...

E2k7
Dear all I have a rather unsettling problem with a Win2k8 SBS server. It was working just fine until yesterday when it started to exhibit strange (apparently) network & AD related problems which led to an eventual failure of the Exchange server, which stopped working reporting: --- Process MAD.EXE (PID=3D2672). Topology discovery failed, error 0x80040a02 (DSC_E_NO_SUITABLE_CDC). Look up the Lightweight Directory Access Protocol (LDAP) error code specified in the event description. To do this, use Microsoft Knowledge Base article 218185, "Microsoft LDAP Error Codes.&qu...

Problem with data sheet and linking it to another sheet to make a report
This is kind of hard to explain, but I will try. I'm making a report in excel by linking the report sheet to another sheet that has the data. What I am doing is entering the equal sign, and then clicking on the data in sheet 2 and put them under the labels I have on sheet 1 (the report). So here are the steps so far: 1. I open excel and design a report with lables and field names, now i just need the data for the report 2. I insert a new sheet that has the data. 3. I go back to the first sheet (report) and put an equal sign into one of the fields where I want the data to appear in my r...

Query based distribution list problem
Running exchange 2003. Our query based lists work fine except the sender gets an error message for accounts that have expired. Is there a way to get around this? TIA...Bob Did you mean accounts that are disabled? Accounts with expired passwords shouldn't have an issue receiving mail, afaik. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Fuzzy Logic" <bob@arc.ab.caREMOVETHIS> wrote in message news:Xns974A7C1C06982bobarcabca@207.46.248.16... > Running exchange 2003. Our query based lists work fine ...

problem in number grouping
hi did anyone try to change the settings of the number grouping? (from xx.xxx.00 to xx.xxx,00) we tried that and got the following problem new product in a quote: ( product A --> decimal supported: 2) after typing in the quantity of product A f.e. 75,25 it switched to 75.025,00 ! But it calculated with 75,25! after refreshing the calculation based on 75.025,00! our regional setttings are english (united states) and we use the win server 2k03. i tried to fix it bye changing the number grouping settings in the regional settings, but it didn't work. Using the english(usa) regional settin...

Outlook Client having problem connecting to exchange
Hi, I have an issue with my Outlook client XP/2003 connecting to my Exchange 2003 servers. I have a clustered Exchange 2003 server sitting in my loca land and 2 Front-end exchange 2003 server in my DMZ. Both Front-end servers are on NLB. Intermittenly, when users launch their outlook they will face the followings: 1. Request login to outlook. The authentication is coming from my front-end server. I can see this on my logon prompt ServerName requesting logon.. 2. When they click on send / receive Oultook XP/2000 users will experience "Waiting to be connected on Front-end Server nam...

Microsoft document Imaging OCR problem
Ive been working with this a new computer lately and have been running into a whole bunch of problems. Right now the main one is I have been trying to get the OCR in my Microsoft Document Imaging to WORK! I can get the OCR to run and even bring up the search box but when even I type what I'm looking for it just highlights the very first thing on the page and moves on through till the very last thing. It worked fine on my windows XP Computer with Microsoft office 2003. At the moment I'm using a Windows 7 home premium andMicrosoft office 2007 Will take any advice!!!! ...

copy and paste problem
Hi there I am trying to serialise an object to the clipboard using RegisterClipboardFormat This works perfectly while my program is running but if I close the program and re-open it, it appears the clipboard still has the data but when I paste the data is somehow corrupted and the program dies. Does anyone have any idea what may be going on Any help will be much appreciated as always Phil You might want to ensure that you are not storing pointer address locations within your object (such as LPCTSTR etc). These locations would be valid as long as your application is alive, but would ex...

2002 to 2003 Conversion problem
Upgrading 2002 to 2003... When I attempt to open my 2002 file I get the "Money File Conversion" window, but when it tries to convert, I keep getting "Money cannot locate... because it is read-only.." My mny file is not read only. If I uninstall and reinstall 2002 the file opens fine. I exported it again (from 2002) and still have the same problem. I ran the salvage utility - sez it ran successfully with a note in the log "Warning: broken records in system table." but it still won't open. In microsoft.public.money, Larry Dusanic wrote: >Upgrading 20...