Results 1 to 4 of 4

Thread: Index + Match/Multi-Vlookup for Items with shared names

  1. #1

    Index + Match/Multi-Vlookup for Items with shared names

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all, I'm having a problem that is giving me a major headache, hoping someone here can help me out.


    In the attached excel file, I'm trying to do a lookup based on two criteria - the class and the number. I used an index/match combo that I think should work, but it didn't register correctly. I keep on getting an error.

    I believe this may be due to the fact that there are shared values - some numbers are shared across multiple classes. If that's the case, is there a workaround so that I can properly lookup the item? If not with the index/match, with any other function? (I've tried a vlookup in this case but no luck either)


  2. #2
    Seeker ExcelTactics's Avatar
    Join Date
    Sep 2013
    Seattle, WA, USA
    Hi Marriot,

    To search multiple criteria, you'll need to structure the INDEX(MATCH) as an array formula:


    Enter as an array formula by keying CTRL+SHIFT+ENTER.
    Like what you just read? Learn how to master Excel with new tips and tutorials each week at

  3. #3
    =INDEX(A4:C18,MATCH(A1&B1,A4:A18&B4:B18,0),3) works just fine but needs array entering
    you can use this non array version instead

    note you only need the column you are returning from not the whole range but
    =INDEX(A4:C18,MATCH(A1&B1,INDEX(A4:A18&B4:B18,0),0),3) would work as well

  4. #4
    Acolyte Kevin@Radstock's Avatar
    Join Date
    Oct 2012
    Excel Version
    Hi marriot

    The LOOKUP function could be another idea.

    This is a non array formula, just enter to commit.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts