vlookup-Closest value

Dear Friends,

How can I use Vlookup to give me the closest value greather than or equal to 
vlookup value.For example if my lookup value is 5 and I have 4.9 and 5.1,5.2
It chooses 5.1 and give me the corresponding value.

Thank you,
0
atatari (2)
2/6/2006 4:57:24 AM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
298 Views

Similar Articles

[PageSpeed] 56

atatari wrote:
> Dear Friends,
> 
> How can I use Vlookup to give me the closest value greather than or equal to 
> vlookup value.For example if my lookup value is 5 and I have 4.9 and 5.1,5.2
> It chooses 5.1 and give me the corresponding value.
> 
> Thank you,

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The default behaviour of Vlookup will;
basically assign everything between (and including) 4.9  and less than 
5.1  to the 4.9 value.   So 4.9 <= x < 5.1
So the value 5 will actually get hooked up with the 4.9 value which does 
not appear to be what you want.

Try using MATCH and INDEX, this is just one work around.
Sort your values in reverse order eg.
Col A,	Col B
5.2	AnswerFor5.2
5.1	AnswerFor5.1
4.9	AnswerFor4.9

So the answer you are after is   =INDEX(B1:B3,MATCH(5,A1:A3,-1))
This will return "AnswerFor5.1"

The -1 tells us to look in descending instead of ascending order.
And you can change the number 5 to point to a cell you wish to look up.
With this method Columns A and B don't even have to be next to each 
other.  B1:B3 could quite easily have been column Z eg. 'Z1:Z3' or even 
in the same column A eg. 'A11:A13'

Hope this helps
George
0
bytesmee (15)
2/6/2006 12:36:59 PM
Not possible using vlookup, in an unsorted list you can use

=INDEX(B1:B10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10,"<"&200)+1),A1:A10,0))

will lookup a value that is greater or equal to 200 in A and return the 
value from B
so if it would work in a vlookup it might have looked like

=VLOOKUP(200,A1:B10,2 and so on

-- 
Regards,

Peo Sjoblom

Portland, Oregon




"atatari" <atatari@discussions.microsoft.com> wrote in message 
news:2E2E2766-9469-41C9-B9EA-A2D39D9D65C8@microsoft.com...
> Dear Friends,
>
> How can I use Vlookup to give me the closest value greather than or equal 
> to
> vlookup value.For example if my lookup value is 5 and I have 4.9 and 
> 5.1,5.2
> It chooses 5.1 and give me the corresponding value.
>
> Thank you, 

0
terre081 (3244)
2/6/2006 4:08:59 PM
By default, if the first column of your lookup range is numeric and
sorted and the fourth parameter in your lookup is set to "true", Excel
will search for an exact match and return the requested value. If it
can't find an exact match it will give you the requested value (column)
of the highest value in column one that is less than the search value.

Here's an example:

=lookup(A1;F1:G5;2;true)

lookup search value
key     from/to
10       00 - 9.999...
20       10 - 29.999...
30       20 - .....

So in order to achieve you result and use vlookup you would need to
redesign your lookup table to put the expected value in the proper
place

Your table (yours reads: anything greater than 4.9 and less than 5.1 =
75.12; but e.g 4.0 would result in #NA)

4.9   123.11
5.1   75.12
5.2   112.80

redesigned (this reads: anything from 0 upto but not including 4.9 =
123.11; anything from 4.9 upto but not including 5.1 = 75.12 etc)

0.0  123.11
4.9  75.12
5.1  112.80
5.2  ???
...

Hope this helps.

Hans

0
hansyt (53)
2/6/2006 5:19:33 PM
"unsorted"

-- 
Regards,

Peo Sjoblom

Portland, Oregon




"flummi" <hansyt@gmx.de> wrote in message 
news:1139246373.444605.86510@f14g2000cwb.googlegroups.com...
> By default, if the first column of your lookup range is numeric and
> sorted and the fourth parameter in your lookup is set to "true", Excel
> will search for an exact match and return the requested value. If it
> can't find an exact match it will give you the requested value (column)
> of the highest value in column one that is less than the search value.
>
> Here's an example:
>
> =lookup(A1;F1:G5;2;true)
>
> lookup search value
> key     from/to
> 10       00 - 9.999...
> 20       10 - 29.999...
> 30       20 - .....
>
> So in order to achieve you result and use vlookup you would need to
> redesign your lookup table to put the expected value in the proper
> place
>
> Your table (yours reads: anything greater than 4.9 and less than 5.1 =
> 75.12; but e.g 4.0 would result in #NA)
>
> 4.9   123.11
> 5.1   75.12
> 5.2   112.80
>
> redesigned (this reads: anything from 0 upto but not including 4.9 =
> 123.11; anything from 4.9 upto but not including 5.1 = 75.12 etc)
>
> 0.0  123.11
> 4.9  75.12
> 5.1  112.80
> 5.2  ???
> ..
>
> Hope this helps.
>
> Hans
> 

0
terre081 (3244)
2/6/2006 6:23:52 PM
Hi Peon,

I said "By default, IF the first column is sorted", not "by default the
first column IS sorted".

:-)

Greatings to Portland!

Hans

0
hansyt (53)
2/6/2006 7:33:01 PM
Reply:

Similar Artilces:

Specifying greater than criteria for numerical values
I have the following SQL for a query. The query returns all values and does not limit it to those greater than 1. Anyone have any ideas??? SELECT [Ships & On Hands].UPC, [Ships & On Hands].[Item Desc 1], Sum([Ships & On Hands].[Range 2 POS Qty]) AS [SumOfRange 2 POS Qty], Count([Ships & On Hands].[Range 1 Curr Str On Hand Qty]) AS [CountOfRange 1 Curr Str On Hand Qty] FROM [Ships & On Hands] GROUP BY [Ships & On Hands].UPC, [Ships & On Hands].[Item Desc 1] HAVING (((Count([Ships & On Hands].[Range 1 Curr Str On Hand Qty]))>1)); ...

Vlookup Return Problem
Hi everyone, I'm new at excell and having a problem with a return on a vlookup formula. I have a workbook with several sheets all the same execpt worksheet 6. Which has my zipcodes, city and state. I want to type in zip and get the exact match and return the city. My formula looks like this: =VLOOKUP(P11,MyLookup,2,FALSE) H11 sheet 1 is where I have entered my formula and want my return to display P11 sheet 1 is where I'm entering my zip On sheet 6 which you see below is my range name MyLookup 2 is column B on sheet 6 False is exact Sheet 6 ...

Filter: Multiple values in a cell
Sorry if this was covered here before - spent long time searching... I'm trying to use excel as a database ;) I have a very long Excel table that lists the features in th application. Easy part: I need to keep an information about the customer wh requested this particular feature and to be able to sort the list o features by the customer name. So the easiest way will be to add a column with the customer name in i and auto-filter by this column. Hard part: one feature can be requested by multiple customers. S somehow I need to put multiple customer names into one cell and stil be able ...

log chart minor gridline values at tick marks
I have an XY scatter graph with log10 scales on both axes. How can I display the minor gridline values at the axes tick marks? Currently, the axes display only the power of 10 values (.1, 1, 10, etc...) Thanks Excel does not allow for minor tick labels. However, you can add a dummy XY series along each axis, with a point at each value where you want a label. Hide the points (no line or marker) and add a data label that shows the value. Position the label appropriately. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ ExcellerZero...

Application.VLookup and External files
I want to use Application.VLookup to find values in an excel spreadsheet on a network. I'm not sure of the syntax, especailly the " or ' and !. Here is what I have: In Sheet1 of Book3.xls I have this in a vba module: Sub FindPrice() cells(a,1) = Application.VLookup("PRM 8018539",'\\myserver01\PRICE UPDATES\[IPRICE 1.xls]'!sheet1.range("B2",Range("C65536").End(xlUp)),false) End Sub Exactly as is returns Compile Error: Expected: expression ant the first single quote in front of myserver. If I change both single quo...

Formatting cell based upon values w/in a selection
Background: I have a spreadsheet which tracks 10+ training course dates for 100+ individuals. All dates have conditional formatting applied to turn red if the date is <=TODAY(), yellow if btwn TODAY()+1 and TODAY()+30, and amber if the date in the cell is btwn TODAY()+31 and TODAY()+60. Dilemma: Column A, for each individual, needs to reflect their overall status (preferrably using the stoplight icon set) in their training. If any of their items are in red, Column A would show the red stoplight...if no items are red but yellow or amber dates are present, Column A would show the ...

Report Help: Summing values in multiple fields
I need some additional help with a report. This database is used by one individual to keep statistics on weekly meetings. Management has requested a report from this database on a periodic basis to show what kind of cases which are being discussed. Below is the structure of the database: ID Date Time Manager Attendees Case Presented By * Name of Case Case Number Violation Category1 % Violation Category2 % Violation Category3 % Recommendation & Additional Comments (1) Is it possible to capture the dates which are put in as part of parameter for use in the report header? (2) The list of i...

Value Axis
Hi, I have a line chart that presents two values - 1.13% and 1.18%. The chart looks fine, but the Value Axis’s numbers, the vertical line or Y, is repeated. Ex. The chart Y: 1.11%, 1.11%, 1.12%, 1.13%, 1.13%...... X: Quarter 1, Quarter 2 Would you please tell me why the numbers on the vertical line is repeated and how to fix the problem? Thanks Chi In the Format Axis dialog box for the Y Axis - tab called "Scale", check the values for the minimum, maximum, and major axis units. By making adjustments to the major axis value, you can eliminate the repeating numbers. Also ch...

Event of changing the grid-cell value.
Hello. In VB.NET 2008. I have dataGridView. Which event occurs when changing data row value. (Not all the times I am changing the value via the dataGridView directly. There are also values that are changed directly via the datatable object. I need a general method, please. dataGridView is connected to BindingSource). Thanks :) In general you should avoid changing values using a datagridview, that is the client area, you would use the object which is the datasource of the bindingsource. I understand from your other messages it is a datatable but it can be any collection. ...

negative time values?
F103=SUM(F94)-(F102) could someone tell me how to format this cell to get the correct answer, current format is [h].mm;#;[Red]-[h].mm; and its giving me a value of 1 and it should be -18:22 F94=218:18 F102=236:40 Thank you, Steve Try using this format: [h].mm;[Red]-[h].mm;#; by default, the second field is for negative numbers, the third for zero. Yours had negative numbers returning a single digit. In article <8Qyec.100$NJ5.0@newsfe5-gui.server.ntli.net>, "Steven" <me@where.why> wrote: > F103=SUM(F94)-(F102) > > could someone tell me how to fo...

conditional counting of values in Excel 2003
Hi everybody I'm trying to make a statistical analisys of the answers to a questionnaire like this: 1. Where are you from? (US) (IT) (DE) 2. Look at this picture. How much do you agree with each of the following statements (1=totally disagree, ..., 4=totally agree) ? 2.a) It's funny (1) (2) (3) (4) (5) ... 2.z) It's obscene (1) (2) (3) (4) (5) Answers have been collected like this (in columns, starting from B): | A | B | C | D | E | F | G | ----|------------+----+----+----+----+----+----+ 1 |Nationality | US | US | IT | US | IT | DE | ----+--------...

Questions On Calling Graphics via Cell Value
Thanks to a tip from Dave Peterson, he directed me to a site that has example code which I've included. There's also a link a the bottom of the referenced page to download a sample worksheet however it doens't appear to be working. (hangs) Here's the reference: http://www.mcgimpsey.com/excel/lookuppics.html Here's the code: Private Sub Worksheet_Calculate() Dim oPic As Picture Me.Pictures.Visible = False With Range("F1") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible...

New page based on value
How can I force a new page return in a report based on the value of [Owner]? [Owner] is from 1 to 22. What I would like to do is make sure the owner only gets his information, so when [Owner] changes I need to issue a new page return so the next owner starts at the top of a new page instead of being mixed with the previous owner's information. Thanks. Rod wrote: >How can I force a new page return in a report based on the value of [Owner]? >[Owner] is from 1 to 22. What I would like to do is make sure the owner only >gets his information, so when [Owner] changes I need...

Call value from table not form
I am probably overlooking something stupid here but, I need to pull a value from a table when a form is not open? Dim InternalEmail As String InternalEmail = Forms!frmCommitteeEmail!DMEmail this gives an error if the form frmCommitteeEmail is not already open. I am pretty sure that is the problem because if I add code to first open the form it works fine. Any help here will be appreciated. Thanks in advance Dave wrote: > I am probably overlooking something stupid here but, I need to pull a > value from a table when a form is not open? > > Dim InternalEmail As String > I...

Search a text cell for a valid value.
Greeting Without using VBA. Is it possible to search a text cell and return the start position within that cell if valid text is found. example If cell A1 had the text "Ringwood North Vic 3134", or "Sydney NSW 2001" The point is that I will not know the cells content, but I want to test if the text contains either Act,NSW,QLD,SA,TAS,Vic,WA and return the starting position of that text. I have tried using Nested IF's with search,find,match etc but once an invalid test is encountered, #value is returned. Any ideas Cheers Tony Ric...

Repeat FORM values from 1 record to next
I would like to design a FORM where the previous record's values show up automatically. I know about CNTL '. But I want to do something in the design mode where 4 of 5 fields just pick up the last record's values. Thanks for the help. You use function: =Dlast("[fieldname]","[Tablename]") on default value property on 4 of 5 fields that you pick. Lisa K-A wrote: >I would like to design a FORM where the previous record's values show up >automatically. I know about CNTL '. But I want to do something in the >design mode where 4 of 5 field...

Storing a color value as a single byte
I have an application that stores colour text to file currently as 4 bytes for each character (Char, Red, Green, Blue). As you can imagine, this creates rather large files and is not ideal. The required selection of colours is minimal and the base 16 colours available as standard in something like MS Paint would be sufficient. Is there a simple way to store a colour as 1 byte rather than 3? I understand that a rounding factor would have to be applied so the colour out may not be exactly the same as the colour in and would end up the nearest match and don=92t see this as a problem...

assign a number value to a text. ie, PL equals .5
I'm using different material "plate, angle, tube steel" I would like to assign a number value to that text. ie. plate would equal .5 Is there a way I can do this? Put the .5 in an un-used cell. Select the cell and then pull-down: Insert > Names > Define and enter plate then click add and click ok. You can then use the name in a formula: =plate*12.345 -- Gary's Student "JWDesigns" wrote: > I'm using different material "plate, angle, tube steel" I would like to > assign a number value to that text. ie. plate would equal .5 Is ther...

change cell format based on another cell's value
Hi, I am trying to change the background colour of a cell based on another cells value. For instance, Column E allows numbers 1,2,3,4,5 or 6 Column F has a nested IF formula =IF((E276=1),"Opportunity",IF((E276=2),"WIP",IF((E276=3),"Submitted",IF((E276=4),"Approved",IF((E276=5),"Committed",IF((E276=6),"Settled","")))))) If a cell in E column has the value "6" I want the adjacent cell in column F ( or the whole row) to change colour to Green. I managed to do this with a Select Case in vba before I created the ...

move a row of data values to a column in sorted order
Does anyone have a method of moving a row of random values to a separate column on the speadsheet in numerical order and capturing the heading fields sot that they are matched. Expample... A B C D 232 121 88 412 --- this is my row data column this is how I want to reformat the row data on the spreadsheet 88 - C 121 - B 232 - A 412 - D Hi, 88 - C Is that all in one cell? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answer...

Excel Charts - Value Y axis scale
Can someone tell me if there is a way to prevent the values on the axis of a chart from changing when new data is added to the series? have 3 charts on a worksheet that are linked to external data. I woul like the Y axis values to remain constant but when the external data i updated, the Y axis values change -- Message posted from http://www.ExcelForum.com Hi, Double click the axis and on the scale tab uncheck the Auto option for Min/Max values Cheers Andy yeagerr < wrote: > Can someone tell me if there is a way to prevent the values on the Y > axis of a chart from changin...

Cell value change runs a macro?
Is there a way to make it so that if a cell value changes, a macro is called? -- Steach91 ------------------------------------------------------------------------ Steach91's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30234 View this thread: http://www.excelforum.com/showthread.php?threadid=553053 Sure You use a Worksheet_Change() event. To access this press alt+f11 to access the VBE, right click on the worksheet concerned and select view code... In the left drop-down in the resulting window select worksheet and in the right one select Change. You wil...

vlookup and if, I think
I have the following data: Client January February March (and so on until December) A 1 1 1 1 1 1 1 1 B 1 1 1 1 1 1 Each time I visit a client, I enter a 1 in the corresponding cell. I want to detail, in a separate range, how many times I visit a client in each month. Trying to clarify a bit more: I want to have a list in a cell with the clients, a list in another cell with the months, and when I choose for example A, February, to obtain a 3. Is this possible? Thanks in advance, Regards, Emece.- Assum...

counting unique text values in an array of data
I have filtered data which contain key text with 3 possible options within each cell, how can I count the text and lookup an additional text in a column next to the cell? ex. f1, f1.5, f2 is the criteria which could be "within" the string of text, there is also a 6 digit number within the same text which can be looked up in a column to the left. If this 6 digit number is the same, the criteria should not count. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click t...

Boolean values in schemas
Hi. I want to to use the xs:boolean type for one of my elements. But is it possible to make these boolean values be read as Yes/No instead of true/false? Should I create a custom type for that? Thank you. Julian According to the XMLSchema datatypes spec, the following are the allowed values for xs:boolean 3.2.2.1 Lexical representation An instance of a datatype that is defined as �boolean� can have the following legal literals {true, false, 1, 0}. If you need the values to be Yes/No it has to be a custom type derived from xs:string with enumerations "Yes" and "No" ...