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.

    Sample.xlsx

    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)

    Thanks!

  2. #2
    Seeker ExcelTactics's Avatar
    Join Date
    Sep 2013
    Location
    Seattle, WA, USA
    Posts
    9
    Articles
    0
    Hi Marriot,

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

    =INDEX(C4:C18,MATCH(1,(A4:A18=A1)*(B4:B18=B1),0))

    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 ExcelTactics.com.

  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

    =INDEX(C4:C18,MATCH(A1&B1,INDEX(A4:A18&B4:B18,0),0))
    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
    Hi marriot

    The LOOKUP function could be another idea.

    =LOOKUP(2,1/((A4:A18=A1)*(B4:B18=B1)),C4:C18)
    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
  •