+ Reply to Thread
Results 1 to 11 of 11

Multiple Criteria - Vlookup for numerical values

Hybrid View

  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):

    =LOOKUP(2,1/(($A$1:$A$9=30)*($B$1:$B$9=1800)),C$1:C$9)
    copy this to next column so as to retrieve column D value (3)
    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
    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)

  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

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

    G2: =LOOKUP(2,1/(($A$1:$A$9=$E2)*($B$1:$B$9=$F2)),C$1:C$9)
    copied to H2

  7. #7
    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)

  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

    Thanks for the catch!

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

+ 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