+ Reply to Thread
Results 1 to 4 of 4

lookup multiple columns for a match

  1. #1
    Registered User
    Join Date
    06-29-2020
    Location
    pa. USA
    MS-Off Ver
    excel 2013
    Posts
    19

    lookup multiple columns for a match

    (SOLVED)I want to search sheet 1 columns A thru F for a match to J2 if found return value in column G of same row as match ex. when the search is performed for M28 on sheet 2 it should find the match in B47 on sheet 1 and then show 46 from L47 on sheet 1 in M28 on sheet 2, I hope this helps clears up what I'm looking for.
    Attached Files Attached Files
    Last edited by vern07; 07-01-2020 at 09:01 PM.

  2. #2
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: lookup multiple columns for a match

    Please try this non array formula.

    M2
    =IFERROR(INDEX(Sheet1!G:G,
    IFERROR(MATCH(J2,Sheet1!A:A,0),
    IFERROR(MATCH(J2,Sheet1!B:B,0),
    IFERROR(MATCH(J2,Sheet1!C:C,0),
    IFERROR(MATCH(J2,Sheet1!D:D,0),
    IFERROR(MATCH(J2,Sheet1!E:E,0),
    IFERROR(MATCH(J2,Sheet1!F:F,0),
    "Not found"))))))),"")

    Regards

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: lookup multiple columns for a match

    Your explanation of what you wanted to happen when a match was found was unclear and confusing. Here is a solution using VBA that I attempted to see inside your head, but my crystal ball was cloudy. Advise if you need anything changed with a clear description of what a solution should look like

    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: lookup multiple columns for a match

    In M2 sheet 2:
    Please Login or Register  to view this content.
    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Quang PT

+ 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 across multiple columns returns value from LOOKUP table
    By RoundaboutCJP in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-20-2019, 05:52 AM
  2. [SOLVED] INDEX MATCH where lookup array is multiple columns
    By RoundaboutCJP in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-23-2017, 05:53 AM
  3. Index Match with multiple criteria (two lookup columns)
    By jason4444 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-09-2016, 03:30 PM
  4. Lookup/match in multiple columns meeting criteria
    By ranmyaku in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-03-2013, 04:36 PM
  5. [SOLVED] Lookup match index multiple values return other columns
    By martypocock in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 11-07-2012, 10:57 PM
  6. [SOLVED] Excel 2007 - Match and Lookup multiple columns
    By james26 in forum Excel General
    Replies: 8
    Last Post: 10-10-2012, 10:24 AM
  7. Index Match lookup thru multiple columns
    By Zimmerman in forum Excel General
    Replies: 6
    Last Post: 07-09-2010, 01:49 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