+ Reply to Thread
Results 1 to 8 of 8

Look up text based on two criterias

  1. #1
    Forum Contributor
    Join Date
    03-14-2005
    Posts
    266

    Look up text based on two criterias

    Hello.

    Ihave a data list containing these columns (See attached excel):

    ToolNr, Machine number, Tool name
    T1, 5011, Drill 10 mm
    T1, 5012, Drill 12 mm
    T1, 5013, Drill 11 mm

    etc....

    IŽd like to make a table with machine numbers and toolnumber like the one below.

    5011 5012 5013 5014 5015 5016
    T1 Drill 10 mm Drill 12 mm Drill 11 mm
    T2
    T3
    T4
    T5
    T6
    T7
    T8

    How can I write a function that looks upp the data list and completes the table with the Tool name for each tool number and machine?

    I have not found a function with several search critierias.....

    /Anders
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,538

    Re: Look up text based on two criterias

    You need a bog standard INDEX MATCH MATCH:

    =INDEX($R$4:$W$11,MATCH(A4,$Q$4:$Q$11,0),MATCH(B4,$R$3:$W$3,0))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Contributor
    Join Date
    03-14-2005
    Posts
    266

    Re: Look up text based on two criterias

    No, that dit not work...
    I added your code in cell R4 and it did not find the tool name "Drill 10 mm"....
    Any clues?

    /Anders

  4. #4
    Forum Contributor
    Join Date
    01-05-2013
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    223

    Re: Look up text based on two criterias

    Type in cell R4
    =IFERROR(INDEX($C$4:$C$8,MATCH(1,($A$4:$A$8=$Q4)*($B$4:$B$8=R$3),0)),"")
    then hit Ctrl+Shift+Enter

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,538

    Re: Look up text based on two criterias

    Oh, I see ... Sorry!

    Which version of Excel are you using?

  6. #6
    Forum Contributor
    Join Date
    03-14-2005
    Posts
    266

    Re: Look up text based on two criterias

    Hi!!!

    This worked! Very much thank you!!

  7. #7
    Forum Contributor
    Join Date
    03-14-2005
    Posts
    266

    Re: Look up text based on two criterias

    I have office 365. Josephteh solved it!
    Thank you for your support!!

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,538

    Re: Look up text based on two criterias

    Administrative Note:

    Please update your forum profile with the version you are using.

    Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

    With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

    Thank you for helping us to help you.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. Display Text With 2 Criterias
    By kwalking in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-28-2019, 12:13 PM
  2. [SOLVED] Macro Find set criterias to a colomn of number, delete the rows that don't meet criterias
    By TAMMY32 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-03-2017, 04:09 PM
  3. [SOLVED] Lookup some text based on three criterias.
    By Perav in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-14-2015, 08:58 AM
  4. [SOLVED] LookUp Text by two criterias
    By dLhx in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-25-2015, 04:36 PM
  5. [SOLVED] count if match based on two criterias not working with text
    By elrophin in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-30-2012, 06:28 PM
  6. [SOLVED] Sum based on 2 different criterias
    By rlkerr in forum Excel General
    Replies: 1
    Last Post: 09-12-2011, 02:28 PM
  7. Excel 2007 : Eliminate based on criterias
    By theebeautiful in forum Excel General
    Replies: 1
    Last Post: 12-17-2010, 03:31 PM

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