+ Reply to Thread
Results 1 to 6 of 6

index match from two columns

  1. #1
    Forum Contributor
    Join Date
    02-12-2014
    Location
    al ain
    MS-Off Ver
    Excel 2016
    Posts
    149

    Cool index match from two columns

    Hello All,
    I want to lookup a value from two different columns and Index Emp. Name as shown under the arrow sign. What would be my formula to retrieve The Emp. Names??
    Thanks.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor ffffloyd's Avatar
    Join Date
    05-20-2008
    Location
    Melbourne, VIC, Australia
    MS-Off Ver
    Office 365
    Posts
    243

    Re: index match from two columns

    See the attached, modified spreadsheet. I have added two "helper columns". One does a match on column B and the other either copies that result into itself or does a match on column C. Then, in your second table, if there is a number in the second helper column, it uses that as an index to fetch the name from the first table, otherwise it prints a message saying "not found".
    Attached Files Attached Files
    _______________
    Floyd Emerson
    Business Intelligence Consultant
    Melbourne, Victoria, Australia

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: index match from two columns

    B13=IFERROR(INDEX(A:A,1/(1/MAX(INDEX(($B$2:$C$5=A13)*ROW($B$2:$C$5),0)))),"")
    Please Login or Register  to view this content.
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Forum Contributor
    Join Date
    02-12-2014
    Location
    al ain
    MS-Off Ver
    Excel 2016
    Posts
    149

    Re: index match from two columns

    That's good but what If my Trip Code columns are not adjacent?
    Attached Files Attached Files

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: index match from two columns

    Having the Trip Code in the same row is enough to pull employee name.
    you need to select all the columns in which you had trip IDs.
    you need to change the colored ones
    =IFERROR(INDEX(A:A,1/(1/MAX(INDEX(($C$2:$F$5=A13)*ROW($C$2:$F$5),0)))),"")

  6. #6
    Forum Contributor
    Join Date
    02-12-2014
    Location
    al ain
    MS-Off Ver
    Excel 2016
    Posts
    149

    Re: index match from two columns

    Great! Thanks a lot..!

+ 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. Looking for an index match formula that looks at two different columns for a match
    By RobinOfLocksley in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-02-2017, 02:20 AM
  2. Replies: 4
    Last Post: 04-14-2017, 07:47 PM
  3. INDEX MATCH MATCH multiple columns with same heading
    By djm198 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-11-2016, 02:34 PM
  4. [SOLVED] Cross Check Columns for Index Match Match
    By Harr in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 12-31-2015, 11:35 AM
  5. [SOLVED] Using Index & Match (or similar) to check two columns for a match
    By dvs in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-30-2015, 07:07 PM
  6. [SOLVED] Match Issue - Consolidate Columns of Data With Match/Index/etc?
    By excelsior123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-03-2014, 05:14 PM
  7. Increment columns in INDEX and MATCH formula - ROWS and COLUMNS
    By nickmangan in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2012, 10:38 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