Page 1 of 13 12311 ... LastLast
Results 1 to 10 of 127

VLOOKUP - Multiple Criteria

This is a discussion on VLOOKUP - Multiple Criteria within the Excel Questions forums, part of the Question Forums category; Hi Everyone, I was wondering whether anyone knew of a way of performing a VLOOKUP function which has multiple criteria. ...

  1. #1
    New Member
    Join Date
    Aug 2006
    Posts
    8

    Default VLOOKUP - Multiple Criteria

    Hi Everyone,

    I was wondering whether anyone knew of a way of performing a VLOOKUP function which has multiple criteria. e.g. the lookup value would have 3 separate criteria and then you put the table array in and the column index.

    Any ideas???

    Thanks.

    Greg.

  2. #2
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,795

    Default Re: VLOOKUP - Multiple Criteria

    You can't use VLOOKUP like that but you can use a different formula, e.g. to find the first row where the criteria is met in columns A, B and C and then return the value from D

    =INDEX(D1:D100,MATCH(1,(A1:A100="x")*(B1:B100="y")*(C1:C100="z"),0))

    confirmed with CTRL+SHIFT+ENTER

  3. #3
    New Member
    Join Date
    Aug 2006
    Posts
    8

    Default Re: VLOOKUP - Multiple Criteria

    Hi,

    This is good, but it doesnt quite return the result I am looking for.

    ID Type Colour Origin
    66566 Apple Green South Africa
    66567 Apple Yellow France
    66568 Apple Green France
    66588 Apple Yellow South Africa

    I don't know how to make an excel table appear here. This is best I could do sorry.

    What I am trying to do is pull the correct ID into another worksheet tab, based on the three criteria's of Type, Colour and Origin. Obviously you have said that a vlookup cannot do this. The match looks good but I can't seem to get it to extract the ids into the other tab.

    Does this make a bit more sense?

    Thanks for your help so far.

    Greg

  4. #4
    New Member
    Join Date
    Aug 2006
    Posts
    8

    Default Re: VLOOKUP - Multiple Criteria

    My apologies,

    I have got it to work now, just needed a few tweaks.

    Also - One thing...the ctrl+shift+enter.....

    If I am using this within an automated process (as in each month it will pick this up when the list of articles changes), does this mean that they won't work??

    Cheers,

    Greg.

  5. #5
    New Member
    Join Date
    Mar 2008
    Location
    Melrose, Ma
    Posts
    36

    Default Re: VLOOKUP - Multiple Criteria

    I had a somewhat similar problem where i wanted to use Vlookup to find a specific security and specific identifier of where it was held and then return the number of shares if both the other arguments held true.

    For example
    "111111" and "Statestreetbank" ; i just concatenated them together and it works for the purpose of what i was trying to do and made the vlookup work to return the # of shares.

  6. #6
    Banned
    Join Date
    Jul 2006
    Location
    Northeast Pennsylvania
    Posts
    3,656

    Default Re: VLOOKUP - Multiple Criteria

    gregula82,

    Sheet1

     ABCD
    1IDTypeColourOrigin
    266566AppleGreenSouth Africa
    366567AppleYellowFrance
    466568AppleGreenFrance
    566588AppleYellowSouth Africa


    Excel tables to the web >> Excel Jeanie HTML 4


    Sheet2

     ABCD
    1TypeColourOriginAnswer
    2AppleGreenFrance66568

    Spreadsheet Formulas
    CellFormula
    D2{=INDEX(Sheet1!A1:A1000,MATCH(1,(Sheet1!B1:B1000=$A2)*(Sheet1!C1:C1000=$B2)*(Sheet1!D1:D1000=$C2),0))}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4


    Have a great day,
    Stan

  7. #7
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,795

    Default Re: VLOOKUP - Multiple Criteria

    Hello Greg,

    Of course Keith's right. If you use a helper column with your three criteria columns concatenated then you can use VLOOKUP....or to avoid CSE you can try a variation on the formula I posted initially

    =LOOKUP(2,1/((A1:A100="x")*(B1:B100="y")*(C1:C100="z")),D1:D100)

    This differs from the INDEX/MATCH approach because it will give the value from column D on the last row where all 3 criteria are satisfied, rather than the first. If you will only ever have 1 row which matches all 3 then that shouldn't make any difference...

  8. #8
    New Member
    Join Date
    Nov 2007
    Posts
    24

    Default Re: VLOOKUP - Multiple Criteria

    Yes, just tried it, and the dates are not coming up, still #N/A

    {=INDEX('This Week'!A1:A1000,MATCH(1,('Last Week'!A4='This Week'!A4)*('Last Week'!B4='This Week'!B4)*('Last Week'!C4='This Week'!C4)))}

  9. #9
    New Member
    Join Date
    Dec 2009
    Posts
    1

    Default Re: VLOOKUP - Multiple Criteria

    Stan - thanks very much for the detailed example. helped a lot.
    regards
    amit kohli

  10. #10
    Board Regular
    Join Date
    Apr 2009
    Location
    OH
    Posts
    139

    Default Re: VLOOKUP - Multiple Criteria

    You can use the Index and Match functions but I believe it is limited to 55,000 configurations. You can also combine the 3 variable into one variable and use vlookup. Try this.

Page 1 of 13 12311 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com