+ Reply to Thread
Results 1 to 18 of 18

Index Match, search 1 criteria across multiple columns

  1. #1
    Registered User
    Join Date
    12-11-2014
    Location
    New York
    MS-Off Ver
    office 2010
    Posts
    71

    Index Match, search 1 criteria across multiple columns

    Hey guys, Im having trouble setting up an index match that searches 1 value across multiple columns. Attaching an example spreadsheet.
    Thanks for the help

    Column A Column B Column C
    a e 1
    b f 2
    c g 3
    d h 4

    Basically want to do an index match that searches any letter from column A&B and returns the number on C. I tried =INDEX(C2:C5,MATCH(F2,A2:B5,0)) where F2 has the desired letter
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,828

    Re: Index Match, search 1 criteria across multiple columns

    Hi,

    One way

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

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,249

    Re: Index Match, search 1 criteria across multiple columns

    Hi izk,

    Put this in G2

    =INDEX($C$2:$C$5,IFERROR(MATCH(F2,$A$2:$A$5,0),0)+IFERROR(MATCH(F2,$B$2:$B$5,0),0))
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    12-11-2014
    Location
    New York
    MS-Off Ver
    office 2010
    Posts
    71

    Re: Index Match, search 1 criteria across multiple columns

    This works great for this example but when I have to search for a value within 60 columns the formula would be impossibly long to write. any suggestions?

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index Match, search 1 criteria across multiple columns

    Try this...

    =SUMPRODUCT((A2:B5=F2)*C2:C5)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,249

    Re: Index Match, search 1 criteria across multiple columns

    ok izk,

    If you have more columns the problem now gets better. Try the formula in G2 after I've added some more columns and put in both number and text into the array.

    Array Lookup to Index from correct row..xlsx

  7. #7
    Registered User
    Join Date
    12-11-2014
    Location
    New York
    MS-Off Ver
    office 2010
    Posts
    71

    Re: Index Match, search 1 criteria across multiple columns

    Tony, your solution only works if there is no numbers in the array only letters such was my example, but I actually need it to work with both numbers and letters.
    MarvinP, your solution works great with another limitation I have to semicolon each row, in my case I have more than 2000 rows and more than 60 columns. So going 1;2;3;4;5, all the way to 3000 is not the best idea. Any other solutions?

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,597

    Re: Index Match, search 1 criteria across multiple columns

    Try this. It will work with numbers and text (they must be unique in the source data).
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit This should shrink and grow with the number of columns, but the range will still need to be manually input.
    Last edited by FlameRetired; 10-20-2016 at 10:54 PM.
    Dave

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,597

    Re: Index Match, search 1 criteria across multiple columns

    Withdrawn by FR. Double posted.

  10. #10
    Registered User
    Join Date
    12-11-2014
    Location
    New York
    MS-Off Ver
    office 2010
    Posts
    71

    Re: Index Match, search 1 criteria across multiple columns

    I am really sorry but it is still not working when I plug it into my large data. I found it easier to just attach the actaul workbook I need to to work in. Here it is (Book1)

    I highlighted in yellow the column where I am typing the formula on sheet2 hope you can help.

    thanks again
    Attached Files Attached Files

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,597

    Re: Index Match, search 1 criteria across multiple columns

    Thank you izk630 for the upload.

    None of the examples you provided until now had concatenated strings containing the lookup values.

    This is a different problem. There is evidence of multiple matches. I am not surprised my formula did not work.

  12. #12
    Registered User
    Join Date
    12-11-2014
    Location
    New York
    MS-Off Ver
    office 2010
    Posts
    71

    Re: Index Match, search 1 criteria across multiple columns

    any ideas how to solve this? index match or vlookups work fine with repeated values, it just returns the first found or max/min if specified.

  13. #13
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,249

    Re: Index Match, search 1 criteria across multiple columns

    Hey izk,

    You don't need the {1;2;3;4} as you can use a vertical range of those numbers instead. See the attached where I've done a range pointing to a vertical range in the formula. You could make a vertical column of 3000 numbers and the formula would work.
    Array Lookup to Index from correct row Better..xlsx

  14. #14
    Registered User
    Join Date
    12-11-2014
    Location
    New York
    MS-Off Ver
    office 2010
    Posts
    71

    Re: Index Match, search 1 criteria across multiple columns

    MarvinP,
    When pluggin in your solution to my larger model it returns the wrong result. It just returns the same values as my index in the same order. I indexed line numbers in the example so you can see.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    12-11-2014
    Location
    New York
    MS-Off Ver
    office 2010
    Posts
    71

    Re: Index Match, search 1 criteria across multiple columns

    MarvinP.
    I figured out that when a value not in the sumproduct range is typed in, you get the first result, then the second and consecutively. Any way for the formula to give me an NA if value not found?

  16. #16
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,597

    Re: Index Match, search 1 criteria across multiple columns

    I missed a detail. Those numbers are already parsed in N:AV. My apologies.

    In the meantime I reworked my formula in the upload Post #10. This must be array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This returns dates, and I don't think it's doing what you want. It matches only the first 'pono' (column A) that it finds. In column W of Sheet2 I array entered this segment of that formula and summed it. There are as many as 16 occurrences of 'pono' numbers.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Did you want the multiple outputs? In the columns to the right of 'result'?

  17. #17
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,249

    Re: Index Match, search 1 criteria across multiple columns

    Hi izk,

    After studying your last attached, could you show some examples of what belongs in your Sheet2 yellow cells. Also could you give an overview of what the problem really is. Something like, "We ship products out and get error back". Sheet 1 is out and Sheet2 is back. We need to see who supplied the shipment.....

    When these problems get this hard, I sometimes find a small adjustment to the tables or using relationship between the tables to get a better answer than what we have suggested above.

  18. #18
    Registered User
    Join Date
    12-11-2014
    Location
    New York
    MS-Off Ver
    office 2010
    Posts
    71

    Re: Index Match, search 1 criteria across multiple columns

    this si great thank you so much!!!

+ 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. 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
  2. Index Match with Multiple criteria in rows and columns
    By amy22x3 in forum Excel General
    Replies: 1
    Last Post: 06-22-2016, 05:22 PM
  3. Ignore Blanks for INDEX/MATCH with multiple criteria search
    By Stephen23 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-15-2015, 09:14 AM
  4. [SOLVED] Return Multiple values from a column with index and match, and search criteria
    By marcusduton in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 02-15-2015, 09:59 PM
  5. Index/Match to search multiple columns
    By anaranjo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-17-2014, 10:52 PM
  6. [SOLVED] INDEX+MATCH with multiple criteria across both rows and columns
    By george_k in forum Excel General
    Replies: 3
    Last Post: 10-26-2012, 04:11 PM
  7. Using Index Match to search multiple columns
    By myshadeofglory in forum Excel General
    Replies: 15
    Last Post: 05-30-2012, 11:53 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