Share via

Vlookup with Multiple Criteria

Anonymous
2020-02-11T19:29:40+00:00

How can I use VLOOKUP with multiple criteria?

Imagine a list of divisions (Division 1, Division 2, Division 3).

Imagine two types of Division (Store or Warehouse)

I want a user to be able to enter their specifics to get a certain Address. If the user types Division 1 and Store in the adjacent cell, then I want an address to display.. If they type Division 1 - Warehouse, a different address will display etc. 

Simply put, is VLOOKUP the method to doing this? How can I go about achieving this?

Division Type Output
Division 1 Store Example Store Address
Division 1 Warehouse Example Warehouse Address
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2020-02-11T22:17:34+00:00

    I'll tell you how I always solve this problem, where I need to use vlookup to match on more than one column. I create a hidden column that includes the concatenated text from the 2 (or more) columns I need to match with spaces between them. Then I just do a simple vlookup on the hidden column.

    I hope this helps.

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2020-02-11T20:31:35+00:00

    To:  Grill

    re:  two criteria lookup

    The X_vLookup function will do that...

    Looks up Division 1 in column(B) which is the 1st column.

    Returns value from Column(D) which is the 3rd column

    Also looks for "Warehouse" in column 2 (LookupValue2)

    When the row for 'Division 1' and row for 'Warehouse' are the same it returns column 3 value

    Using Row(1:1) for the occurrence number.  It increments as the formula is filled down and if there is more than one row that meets  the criteria, it returns them all. (dash when all found)

    It is free and part of the 'Custom_Functions' add-in, download from MediaFire (no ads)...

    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-02-11T22:33:00+00:00

    Here is a similar solution to Nothing Left To Lose's 2nd answer, but you don't need to use an array formula:

    =INDEX(D3:D6,MATCH(F2&"-"&G2,INDEX(B3:B6&"-"&C3:C6,),0))

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2020-02-11T21:38:33+00:00

    To:  grill

    re:  no download

    Give the array formula below a try.

    It uses the layout in the image from my previous post and

       F2 and G2 contain... " Division 1" and "Warehouse"

    =INDEX($D$3:$D$6,MATCH(1,($B$3:$B$6=F2)*($C$3:$C$6=G2),0))

    '---

    The new (free) Excel workbook "Professional_Compare" is now available at MediaFire.(compares every cell Or every row in two worksheets - choice of compare type - very fast)Download (no ads) from:*http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents*

    0 comments
  3. Anonymous
    2020-02-11T20:34:21+00:00

    Thanks but how can i achieve this without having to download anything?

    0 comments