Dear,
Please find herewith VBA vlookup
one
Value look up in column 1 6
Value look up in column 2 vlookup2(F10, F11, tLookupDemo, 3) Result
Option Explicit
''
***************************************************************************
'' Purpose : Test VLOOKUP2 function
'' Written : 01-Nov-2001 by Andy Wiggins, Byg Software Limited
'' Notes : To see the reuslts, ensure the "Immediate" window is open
(Ctrl+G)
''
Sub Test_Vlookup2()
ThisWorkbook.Activate
Sheets("Lookup in two columns").Select
''Test two numeric parameters
Debug.Print "(1) " & VLOOKUP2(3, 5, Range("tLookupDemo"), 3)
''Test alpha and numeric parameter
Debug.Print "(2) " & VLOOKUP2("One", 4, Range("tLookupDemo"), 3)
''Test two valid parameters
If IsError(VLOOKUP2(3, 5, Range("tLookupDemo"), 3)) Then
Debug.Print "(3) " & "Is error"
Else
Debug.Print "(3) " & "Okay"
End If
''Test with an invalid parameter
If IsError(VLOOKUP2(33, 5, Range("tLookupDemo"), 3)) Then
Debug.Print "(4) " & "Is error"
Else
Debug.Print "(4) " & "Okay"
End If
End Sub
''
***************************************************************************
'' Purpose : Lookup function based on two columns / Demonstrate use of
Evaluate
'' Written : 30-Oct-2001 by Andy Wiggins, Byg Software Limited
'' Amended : 09-May-2002 by Andy Wiggins
''
Function VLOOKUP2(pVal1, pVal2, pRng As Range, pInd As Integer)
''The lookup values refer to columns 1 and 2 in the range
Application.Volatile
Dim lStr_Seek As String
Dim lStr_Col1 As String
Dim lStr_Col2 As String
Dim lStr_Col3 As String
''If an error occurs with "Evaluate" it isn't passed to this function's
error handler
''This handler will pick up any other errors that may occur
On Error GoTo Error_VLOOKUP2
''The quotes enure strings are treated as such and NOT as range names
lStr_Seek = """" & pVal1 & ":""&""" & pVal2 & """"
lStr_Col1 = pRng.Columns(1).Address
lStr_Col2 = pRng.Columns(2).Address
lStr_Col3 = pRng.Columns(pInd).Address
VLOOKUP2 = Evaluate("index(" & lStr_Col3 & ",match(" & lStr_Seek & "," &
lStr_Col1 & "&"":""&" & lStr_Col2 & ",0))")
Exit Function
Error_VLOOKUP2:
VLOOKUP2 = Err
End Function
On Mon, Oct 11, 2010 at 5:34 PM, Pranav Vashishtha <pranav...@gmail.com>wrote:
> Dear Alan
> Apply tis formula and your problem will be over.
> VlOOKUP Function needs 4 parameters your 1st,2nd,& 4th parameters are
> correct but in the 3rd parameter (Column no.) you supplied the column
> address that is $E:$E. I have just changed it to column no. 2 which is
> second column of your table where you want to look up for data.
>
> =VLOOKUP($A2,$D$2:$E$9,2,FALSE)
>
> Hope that helps!
>
> Warm Regards,
>
> Pranav
>
>
> On Sun, Oct 10, 2010 at 2:30 AM, Alan <jalantho...@verizon.net> wrote:
>
>> I am using Excel 2007 and having a strange problem with the VLOOKUP
>> function. I have never used it before, but I have looked at examples
>> on the Internet. However, I cannot figure it out.
>>
>> I placed the following formula in Cell B2:
>>
>> =VLOOKUP($A2,$D$2:$E$9,$E:$E,FALSE)
>>
>> and then copied it to B3 and B4, which respectively show up as:
>>
>> =VLOOKUP($A3,$D$2:$E$9,$E:$E,FALSE)
>> =VLOOKUP($A4,$D$2:$E$9,$E:$E,FALSE)
>>
>> My lookup table is located at D2:E9, with what I am looking up in
>> column D, and the value for the lookup in column E.
>>
>> I placed the second entry in the table in cells A2, A3, and A4.
>> The VLOOKUP functions in cells B2, B3, and B4 show the following
>> results:
>>
>> B2: #VALUE!
>> B3: <second entry in the table>, same as in A3
>> B4: the correct lookup value.
>>
>> This does not make any sense to me, based on the examples I have
>> seen.
>>
>> What am I doing wrong? Thanks, Alan
>>
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>>
>> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>>
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
