+ Reply to Thread
Results 1 to 10 of 10

Index Lookup 2 Criteria with Few Strings Can be Match with Any Criteria

  1. 02-23-2021, 04:30 AM #1
    uckycool
    uckycool is offline
    Registered User
    Join Date
    10-01-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    34

    Index Lookup 2 Criteria with Few Strings Can be Match with Any Criteria

    Hi all good people!

    Screen Shot 2021-02-23 at 15.15.11.png

    I need to lookup 2 criteria using Index and Match but I need one string, for example "apple" (see the image/file attachment) will result in the desired string "FRUIT 1" if the second criteria match any string, while the other string "orange" will result in the desired string "FRUIT 2" or "FRUIT 3" if the second criteria match specific string as shown in the Dimension Table.


    Hope anyone here can help me.
    Thank you..
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-23-2021, 04:52 AM #2
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Index Lookup 2 Criteria with Few Strings Can be Match with Any Criteria

    Try:

    =IFERROR(LOOKUP(2,1/($H$5:$H$7=B5)/($I$5:$I$7=C5),$J$5:$J$7),$J$5)
    Quang PT
    Reply With QuoteRegister To Reply
  3. 02-23-2021, 05:08 AM #3
    uckycool
    uckycool is offline
    Registered User
    Join Date
    10-01-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    34

    Re: Index Lookup 2 Criteria with Few Strings Can be Match with Any Criteria

    Thank you Quang for answering.
    But I will need "orange" to still lookup using 2 criteria (but that will not be the case for apple. For apple it will be like neutralize the 2nd criteria).
    The orange in the last row here should return with #N/A.
    Attachment 720204

    Maybe should the adjustment also be made in the dimension table (plus some another adjustment in the formula)?
    I've tried to use an asterisk as the wildcard but no luck.

    Hope you have a further solution!
    Reply With QuoteRegister To Reply
  4. 02-23-2021, 05:11 AM #4
    uckycool
    uckycool is offline
    Registered User
    Join Date
    10-01-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    34

    Re: Index Lookup 2 Criteria with Few Strings Can be Match with Any Criteria

    Screen Shot 2021-02-23 at 16.01.34.png

    here is the screenshot of the result
    Reply With QuoteRegister To Reply
  5. 02-23-2021, 05:17 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Index Lookup 2 Criteria with Few Strings Can be Match with Any Criteria

    if there is just ONE "wildcard" fruit (apple) try this:

    =INDEX($H$5:$H$7,MATCH(1,($F$5:$F$7=B5)*($G$5:$G$7=IF(B5="apple","",C5)),0))
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  6. 02-23-2021, 05:18 AM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Index Lookup 2 Criteria with Few Strings Can be Match with Any Criteria

    In YOUR version of Excel, y formula may need to be an array formula. this one should NOT require array entry:

    =INDEX($H$5:$H$7,MATCH(1,INDEX(($F$5:$F$7=B5)*($G$5:$G$7=IF(B5="apple","",C5)),0),0))
    Reply With QuoteRegister To Reply
  7. 02-23-2021, 05:22 AM #7
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Index Lookup 2 Criteria with Few Strings Can be Match with Any Criteria

    Try again:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 02-23-2021, 05:49 AM #8
    uckycool
    uckycool is offline
    Registered User
    Join Date
    10-01-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    34

    Re: Index Lookup 2 Criteria with Few Strings Can be Match with Any Criteria

    Quote Originally Posted by bebo021999 View Post
    Try again:

    Please Login or Register  to view this content.
    AMAZING! it solves my need!
    Thanks soooo much Quang
    Reply With QuoteRegister To Reply
  9. 02-23-2021, 05:51 AM #9
    uckycool
    uckycool is offline
    Registered User
    Join Date
    10-01-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    34

    Re: Index Lookup 2 Criteria with Few Strings Can be Match with Any Criteria

    Quote Originally Posted by Glenn Kennedy View Post
    In YOUR version of Excel, y formula may need to be an array formula. this one should NOT require array entry:

    =INDEX($H$5:$H$7,MATCH(1,INDEX(($F$5:$F$7=B5)*($G$5:$G$7=IF(B5="apple","",C5)),0),0))
    Thank you for your answer Glenn!
    Reply With QuoteRegister To Reply
  10. 02-23-2021, 05:54 AM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Index Lookup 2 Criteria with Few Strings Can be Match with Any Criteria

    You're welcome.


    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Lookup Multiple Criteria using Index-Match in VBA
    By ykobure in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-21-2015, 01:41 AM
  2. Lookup with criteria / Index match (?) / Using dates
    By RenanCip in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-28-2014, 11:23 AM
  3. [SOLVED] index lookup problem against 2 criteria (match)
    By Heinrich Venter in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-28-2014, 09:41 AM
  4. Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail
    By sfike in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  5. Multiple Criteria Index/Match Function in VBA Looking Up Dates/Strings/Numbers
    By nadstradamus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-21-2014, 12:35 PM
  6. Help with Lookup/match/index with 2 criteria.
    By Fizziii in forum Excel General
    Replies: 5
    Last Post: 04-28-2011, 10:00 AM
  7. Lookup Or Index, Match then Sum with several criteria.
    By borissinga in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-15-2010, 08:31 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1