+ Reply to Thread
Results 1 to 11 of 11

Multiple Criteria - Vlookup for numerical values

  1. #1
    Registered User
    Join Date
    10-18-2007
    Posts
    5

    Unhappy Multiple Criteria - Vlookup for numerical values

    I have the following 'numerical values'

    25 1000 0.01 1
    30 1000 0.01 1
    35 1000 0.01 1.3
    25 1800 0.03 2.5
    30 1800 0.04 3
    35 1800 0.04 3.7
    25 3000 0.12 5.5
    30 3000 0.14 5.8
    35 3000 0.17 6.2

    With the first two values as criterias, I need to find the 3rd and 4th value
    Example: If I have the criteria as 30 and 1800 , I should get the result as 0.04and 3

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Multiple Criteria - Vlookup for numerical values

    You don't state where your table is located, let's say for args sake the table is located in A1:D9, to retrieve column C value (0.4):

    Please Login or Register  to view this content.
    Obviously references to 30 & 1800 can be to cell references containing those values of interest.

    Going forward please also specify XL version either in your profile or in your post.
    Last edited by DonkeyOte; 08-28-2009 at 05:11 AM. Reason: edited per arthurbr's catch (post # 5)

  3. #3
    Registered User
    Join Date
    10-18-2007
    Posts
    5

    Re: Multiple Criteria - Vlookup for numerical values

    Thanks for your reply
    It worked !! I was exactly looking for this solution .

    Now, how do I put this in form of a VBA code ?
    Can I use <Application.WorksheetFunction.Lookup> and specify ranges?

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Multiple Criteria - Vlookup for numerical values

    Can you elaborate in terms of how you intend to use the function in terms of VBA - ie presumably certain elements of the formula will be utilising variables in the VBA etc ?

  5. #5
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Multiple Criteria - Vlookup for numerical values

    DO,isn't ther a parenthesis missing ?

    =LOOKUP(2,1/(($A$1:$A$9=30)*($B$1:$B$9=1800)),C$1:C$9)

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Multiple Criteria - Vlookup for numerical values

    Thanks for the catch!

    (I've edited my original accordingly to avoid confusion in case of future reference!)

  7. #7
    Registered User
    Join Date
    10-18-2007
    Posts
    5

    Re: Multiple Criteria - Vlookup for numerical values

    The two values are got as an input from the user and I want the macro to lookup for values and return the results

    Example : User enters values 30 and 1800 in say e2 and f2 cells
    I want the VBA code to check for the values and put it in say g2 and h2

    I am securing the main table values from the user (the array basically)

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Multiple Criteria - Vlookup for numerical values

    But my question is: why VBA ? Why not just put the formula into G2/H2 ?

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    10-18-2007
    Posts
    5

    Re: Multiple Criteria - Vlookup for numerical values

    In the formula, there is any "array" and the user would easily comes to know the values in the table (to be secured). Hence it wouldn't be appropriate to mention it in the formula which can be visible.

    The VBA code would be required as I am going to link 2 workbooks. Workbook with the standard table, and workbook where user inputs values..

  10. #10
    Registered User
    Join Date
    10-18-2007
    Posts
    5

    Re: Multiple Criteria - Vlookup for numerical values

    I tried the following in VBA code

    Dim ScHeatgen as Integer
    Dim SourceWB2 As Workbook

    ScHeatgen = Application.WorksheetFunction.Lookup(2, 1 / ((SourceWB2.Worksheets(1).Range("a1:a55") = 30) * (SourceWB2.Worksheets(1).Range("b1:b55") = 1800)), SourceWB2.Worksheets(1).Range("c1:c55"))

    I tried to replace 30 and 1800 with variables, it still didn't work ???

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Multiple Criteria - Vlookup for numerical values

    Per forum rules whenever posting code encase within CODE Tags.

    Why not store the "top secret" array in a Sheet with visibility setting set via VBA to xlVeryHidden ? Thereby avoiding links altogether. You could even use VBA Change event against alteration of E2/F2 to invoke the calculation, performed in VBA with only value returned to G2/H2.
    Independent workbooks are IMO generally a good idea.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1