vlookup Excel 2000

[UrbaneRove]

Hey there,

It may be a simple question but here goes.

list in excel

1 100
3 300
6 600

using vlookup with the first field information.

1 will return 100
2 will return 100
3 will return 300
4 will return 300
5 will return 300
6 will return 600
greater will return 600


I would like to have vlookup return the greater value if it is in between numbers on the list.

ie.
3 will return 300
4 will return 600
etc

I haven't found anything on it as of yet so any help is very appreciated.

UrbaneRove

 

[Dreamboat]

Try checking here:

http://www.thewordexpert.com/downloads.htm#MyVlookup

I'm pretty sure I talk about the default behavior of a vlookup, and I think that's it. Anne Troy
Dreamboat@TheWordExpert.com
Anne@MrExcel.com

 

[UrbaneRove]

Thanks for the reply Anne.

What I am looking for is a way to work with vlookup and push past the default behaviour. I understand that the function will find the closest value without exceeding it but what I need is vlookup to find the closest value that is equal to OR DOES exceed it.

Any ideas??

UrbaneRove

 

[DaleWatson123321]

Hi UrbaneRove,

If the values in your lookup table are numbers you have control over (as opposed to data that might be continually updated from another source), then the following could be a simple workable option.

Basically what you need to do, is to insert a row between each of the existing numbers in your table. Then in each inserted row, add formulas that reference the cells above and below. Let me explain...

For example, if your original table is as you described

1 100
3 300
6 600

and these values are in cells A1:B3, then your new table will contain:

A1 and B1 - no change
A2 - this formula: =A1+.00001
B2 - this formula: =B3
A3 and B3 - no change (was row 2, before row insertion)
A4 and B4 - copy the formulas from A2:B2

Repeat the two steps: a) insert row, and b) copy formula, for each row of your table.

I hope this makes sense, and is workable for you. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[rmikesmith]

UrbaneRove,

As an alternative, try out the following custom function, a direct replacement for VLookup that will do what you want. Put this into a standard code module and you will be able to use it as a worksheet function (or you can call it from within a VBA procedure):

Function MVLookup(ByVal Lookup_Value As Variant, ByRef Table_Array As Range, ByVal Col_Index_Num As Integer) As Variant
Dim Result1 As Variant
Dim Result2 As Variant
Dim Rng As Range

  With Application
    Result1 = .VLookup(Lookup_Value, Table_Array, Col_Index_Num, False)
    If Not IsError(Result1) Then
      MVLookup = Result1
      Exit Function
    ElseIf Result1 <> CVErr(2042) Then
      MVLookup = Result1
      Exit Function
    End If

    Result2 = .VLookup(Lookup_Value, Table_Array, Col_Index_Num, True)
    If IsError(Result2) Then
      MVLookup = CVErr(Result2)
      Exit Function
    End If
    Set Rng = Range(Cells(Table_Array.Row, Col_Index_Num), Cells(Table_Array.End(xlDown).Row, Col_Index_Num))
    Result1 = .Match(Result2, Rng, 0)
    Result1 = Rng.Rows(Result1 + 1)
    If IsEmpty(Result1) Then
      Result1 = Result2
    End If
    MVLookup = Result1
  End With
End Function

Let me know if you have any questions. I have tried to test it thoroughly, but there may be a critter or two lurking.

Regards,
M. Smith

 

[Dreamboat]

Hey, M. Smith. If you have nothing better to do than give away code, I could use some help ova heah!!!

LOL Anne Troy

http://www.TheWordExpert.com

http://www.Brainbench.com

 

[rmikesmith]

Hi Dreamboat,

Thanks for the compliment (it was a compliment, wasn't it?)

IF

=

THEN

I've been checking out your site; very impressive and since I usually only scratch the surface of Word's functionality, I'm sure it will be a great resource. I've even bookmarked it (is that committment or what?).

Regards,
Mike

 

[Dreamboat]

Lots of C words flying around there, Mikey.



It was definitely a compliment.
And you should email me.

Anne Troy

http://www.TheWordExpert.com

http://www.Brainbench.com

 

[UrbaneRove]

Thanks for all the response and the code snippet.

I did find a little smaller answer.

Most of the programming I do is in VB and VBA with Access and not Word or Excel

Inserting new rowswas too extensive to do as the lookup table is about 5000 entries as it is. The code snippet I will definitally be looking at ... Thank you.

What we can up with here is ;

=INDEX(Lookup_table,MATCH(lookup_value,Lookup_table,1)+1,1)

'temp example
=INDEX($D$4:$D$9,MATCH(G4,$B$4:$B$9,1)+1,1)

using the match and index funtions.

First get the line that matchs the value closest without exceeding it from the lookup table using the MATCH function. Add one to this. Then using the INDEX function return the value there. It works fine and has been easy to implement.


Thanks all.

Urbane Rove

 

[rmikesmith]

Urbane,

Your worksheet function, as written, doesn't return the correct value if the lookup value is an exact match. The following modification corrects this, although it still doesn't satisfy your requirement of ">6 returns 600" and instead produces the #Ref error.

=IF(NOT(ISERROR(MATCH(Lookup_Value,Lookup_Range,0))),INDEX(Lookup_Table,MATCH(Lookup_Value,Lookup_Range,0),2),INDEX(Lookup_Table,MATCH(Lookup_Value,Lookup_Range,1)+1,2))

My custom function handles this last requirement and has the advantage of ease of use; i.e., you would replace the above with

=MVLookup(Lookup_Value,Lookup_Table,Column_To_Return)

Hope this helps
Mike