+ Reply to Thread
Results 1 to 2 of 2

I need nearest value Lookup data using multiple conditions

  1. #1
    Registered User
    Join Date
    11-13-2013
    Location
    Nepal
    MS-Off Ver
    Excel 2010
    Posts
    3

    Post I need nearest value Lookup data using multiple conditions

    Hello,

    I am using following formula to find to Lookup exact match using multiple condition
    =INDEX(K3:K61,MATCH(1,(B3:B61=N2)*(I3:I61=N3),0))

    But now i need nearest match value. Please Help.

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: I need nearest value Lookup data using multiple conditions

    =INDEX(K3:K61,MATCH(1,(B3:B61=N2)*(I3:I61=N3),1))
    or
    =INDEX(K3:K61,MATCH(1,(B3:B61=N2)*(I3:I61=N3),-1))

    Depentdant on if you want the nearest that is higher or nearest that is lower.

    Data needs to be in order, from the excel help file:
    MATCH(lookup_value,lookup_array,match_type)

    Lookup_value is the value you use to find the value you want in a table.

    Lookup_value is the value you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.

    Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

    Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.

    Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

    If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

    If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

    If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

    If match_type is omitted, it is assumed to be 1.

    Remarks

    MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b" within the array {"a","b","c"}.
    MATCH does not distinguish between uppercase and lowercase letters when matching text values.
    If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
    If match_type is 0 and lookup_value is text, lookup_value can contain the wildcard characters asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.

  3. #3
    Registered User
    Join Date
    11-13-2013
    Location
    Nepal
    MS-Off Ver
    Excel 2010
    Posts
    3

    Smile Re: I need nearest value Lookup data using multiple conditions

    Hi yudlugar,

    Thank you very much for your answer.

    Yubraj

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 06-30-2014, 06:29 PM
  2. Lookup with Multiple Conditions
    By anilswarrier in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-20-2012, 01:38 PM
  3. Data lookup with multiple conditions - array for text AND numbers?
    By Armitage2k in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-22-2011, 12:55 PM
  4. Lookup with multiple conditions
    By Svenvlad in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-02-2006, 01:35 PM
  5. Lookup with multiple conditions
    By Sachin Narute in forum Excel General
    Replies: 2
    Last Post: 08-02-2005, 03:05 AM

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