+ Reply to Thread
Results 1 to 6 of 6

Lookup Function with Multiple Criteria

  1. #1
    Registered User
    Join Date
    12-08-2017
    Location
    United States
    MS-Off Ver
    2016
    Posts
    53

    Lookup Function with Multiple Criteria

    Hello, Can anyone explain why Lookup does not work with the second function but does with the first? When I evaluate the wrong formula, the lookup value matches a value in the lookup array, but produced #N/A anyway.

    Correct formula: =LOOKUP(1, 1/(F3&G3=C3:C12&D3:D12), B3:B12)

    Wrong formula: =LOOKUP(F3&G3, C3:C12&D3:D12, B3:B12)

    When I look up D.M. and QFC, the wrong formula gives me an #N/A. When I look up Tacoma and QFC, I get Mo with the wrong formula, when it should be Phil. With the other cities I get an #N/A.

    Thanks for any help.
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Lookup Function with Multiple Criteria

    Try this
    in F6
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    in F8
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by AlKey; 03-02-2018 at 08:55 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Lookup Function with Multiple Criteria

    The 'Lookup' function has two syntaxes: for vector and array.
    You used a vector, but in the form of an array, so excel does not know what's going on.
    See contextual help for this function.

  4. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Lookup Function with Multiple Criteria

    LOOKUP requires the input array be in ascending order.

    So because your input ranges in the "Wrong" formula are generate strings that are not sorted alphabetically, the Lookup is throwing an error because the data is not ascending. (Another common behavior in a non-ascending case is returning a "wrong" value).

    In the "Correct" formula there, the range assesses down to an array of $N/A errors with one numerical result; because Lookup ignores the input errors, it returns the last man standing.

    This requirement that LOOKUP be fed ascending data only is why it has been technically depreciated for twenty years.
    As a rule,
    don't use LOOKUP, VLOOKUP, or HLOOKUP.
    The INDEX(MATCH) construction can do everything those functions can do and better; so why bother remembering these other, less-good functions?
    (The exception is, clever constructions that take advantage of LOOKUP's behavior to do non-standard things, typically the LOOKUP(2, 1/range) construction like you have there).
    Last edited by ben_hensel; 03-02-2018 at 08:58 PM.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  5. #5
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: Lookup Function with Multiple Criteria

    And why not?
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    12-08-2017
    Location
    United States
    MS-Off Ver
    2016
    Posts
    53

    Re: Lookup Function with Multiple Criteria

    Quote Originally Posted by ben_hensel View Post
    LOOKUP requires the input array be in ascending order.

    So because your input ranges in the "Wrong" formula are generate strings that are not sorted alphabetically, the Lookup is throwing an error because the data is not ascending. (Another common behavior in a non-ascending case is returning a "wrong" value).

    In the "Correct" formula there, the range assesses down to an array of $N/A errors with one numerical result; because Lookup ignores the input errors, it returns the last man standing.

    This requirement that LOOKUP be fed ascending data only is why it has been technically depreciated for twenty years.
    As a rule,
    don't use LOOKUP, VLOOKUP, or HLOOKUP.
    The INDEX(MATCH) construction can do everything those functions can do and better; so why bother remembering these other, less-good functions?
    (The exception is, clever constructions that take advantage of LOOKUP's behavior to do non-standard things, typically the LOOKUP(2, 1/range) construction like you have there).
    Thank you for the thorough explanation. I was watching a tutorial about LOOKUP but from what you wrote there seems to be no point in using or remembering it. Thank you and everyone else who responded. Though I didn't understand how I used a vector in the form of an array, since I thought vectors are all arrays. When I sorted the data alphabetically the function worked, so I guess that was the main issue.

+ 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. [SOLVED] LOOKUP function for multiple criteria
    By dandosa in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-11-2017, 11:53 AM
  2. Lookup function with multiple criteria
    By xrajncajnx in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-03-2015, 03:28 PM
  3. [SOLVED] Lookup Function with Multiple Criteria Including Values Between Two Dates
    By webfeet2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-14-2014, 12:01 PM
  4. Lookup or match function with multiple criteria giving different results
    By Mian USman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-24-2013, 09:15 AM
  5. In need of a lookup function that meets multiple criteria
    By shepherdc2814 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-16-2013, 07:16 PM
  6. Multiple criteria lookup function
    By daniel_ev in forum Excel General
    Replies: 2
    Last Post: 07-02-2012, 08:05 AM
  7. use IF/Lookup function in multiple criteria
    By harboens in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-30-2009, 03:50 AM

Tags for this Thread

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