+ Reply to Thread
Results 1 to 13 of 13

Lookup OR Index Match

  1. #1
    Forum Contributor
    Join Date
    05-06-2015
    Location
    Sri Lanka
    MS-Off Ver
    2016, 2007
    Posts
    135

    Lookup OR Index Match

    Thanks in advance for any comments,
    I need this help badly.

    I have three columns

    Company | Rank | Name

    and I have data

    What is the formula if I want the name of the person of specific rank (I will refer a cell as the input) of a specific company (I will refer a cell to get this data). Image and excel file attached.
    Capture.PNG
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Lookup OR Index Match

    Hello
    Try this array formula, entered with Ctrl+Shift+Enter, in cell F3:

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


    If entered correctly, you will see curly brackets {} around the formula. Alternatively, you could use the DGET function but you would need another range of cells to link to the drop downs for the functions's criteria range.

    DBY

  3. #3
    Forum Contributor
    Join Date
    05-06-2015
    Location
    Sri Lanka
    MS-Off Ver
    2016, 2007
    Posts
    135

    Re: Lookup OR Index Match

    it works, thanks. I am grateful.
    a kind brief explain on how this works would be in immense help too.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Lookup OR Index Match

    Enter this formula in F3 with Ctrl + Shift + Enter (Array formula)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This combines the values in F1 and F2 and looks for the combined values in columns A and B and returns the corresponding value from Column C
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Forum Contributor
    Join Date
    05-06-2015
    Location
    Sri Lanka
    MS-Off Ver
    2016, 2007
    Posts
    135

    Re: Lookup OR Index Match

    thank you very much

  6. #6
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Lookup OR Index Match

    The IF function returns:

    {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;13;FALSE;FALSE;FALSE}

    Where it tests for matches of Company and Rank in the Table1 'Company' and 'Rank' fields. As there is only one match it returns its Row number and FALSE for all the non-matching rows. The MIN function ignores FALSE and returns ( in the above example in red) the number 13. This is used by the INDEX function as its row number when returning a name from the 'Name' field.

    Hope that makes some sense. Thank you for the Rep mark

    DBY

  7. #7
    Forum Contributor
    Join Date
    05-06-2015
    Location
    Sri Lanka
    MS-Off Ver
    2016, 2007
    Posts
    135

    Re: Lookup OR Index Match

    Yes, it does, Thank you for your kindness. God bless you.

  8. #8
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318

    Re: Lookup OR Index Match

    Or a non array formula:

    =IFERROR(INDEX(Table1[Name],MATCH(1,INDEX((Table1[Company]=F1)*(Table1[Rank]=F2),0),0)),"")

  9. #9
    Forum Contributor
    Join Date
    05-06-2015
    Location
    Sri Lanka
    MS-Off Ver
    2016, 2007
    Posts
    135

    Re: Lookup OR Index Match

    This is great. Thank you Indi_Ra.

  10. #10
    Valued Forum Contributor
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2010
    Posts
    505

    Re: Lookup OR Index Match

    See more two formulas.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    05-06-2015
    Location
    Sri Lanka
    MS-Off Ver
    2016, 2007
    Posts
    135

    Re: Lookup OR Index Match

    Thanks All,
    It's wonderful to see how many options we have to solve the same problem.

    While thanking you all, among all these options I was amazed to see how nice the newdoverman's solution after I use these name ranges (I have converted the table in to a range and named Company column as CompanyList and Rank Column as RankList, Name Column as NameList, and the input cell of Company and Ranks to InputComany and InputRank.)

    Then the formula became looks like this.

    =Index(NameList,Match(InputCompany&InputRank,CompanyList&RankList,0)

    wow, formula itself tells us what its doing....


  12. #12
    Forum Contributor
    Join Date
    07-12-2015
    Location
    Dubai,UAE
    MS-Off Ver
    2007,Office 365
    Posts
    344

    Re: Lookup OR Index Match

    Hi Dineth,

    A version using SUMPRODUCT...


    =OFFSET(C2,(SUMPRODUCT((Table1[Company]=F1)*(Table1[Rank]=F2),ROW(Table1[Name])))-2,0,1,1)

  13. #13
    Forum Contributor
    Join Date
    05-06-2015
    Location
    Sri Lanka
    MS-Off Ver
    2016, 2007
    Posts
    135

    Re: Lookup OR Index Match

    chullan88, thank you

+ 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] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  2. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  3. Replies: 3
    Last Post: 06-17-2013, 12:37 PM
  4. Match/Index/Lookup - Searching From Bottom to Top (A reverse lookup maybe)
    By Neutralizer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-02-2013, 03:55 AM
  5. Replies: 7
    Last Post: 06-19-2011, 12:51 PM
  6. Lookup/Index-Match-Match using segments
    By BRISBANEBOB in forum Excel General
    Replies: 11
    Last Post: 06-10-2009, 03:45 AM
  7. Using Lookup instead of Index/Match for left lookup
    By teylyn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-07-2008, 09:20 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