+ 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
    1

    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
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    427

    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
    My English is very poor, so please be patient >_<"

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    17,725

    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.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,439

    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).

+ 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