Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VLookup Criteria (XL97; SR2)

    In the following table is it possible to have a VLookup formula with two sets of criteria?
    <table border=1><td>Unit</td><td>UnitDescr</td><td>Type</td><td>Amt</td>
    <td>X</td><td>abcd</td><td>14</td><td>100</td><td>Y</td><td>efgh</td><td>16</td><td>29</td><td>X</td><td>abcd</td><td>21</td><td>75</td>
    </table>

    I would like to have a VLookup formula use the field criteria of the "Unit" as well as "Type" to return a result. In this case "X" and "14" to return 100.

    Note! That there are two records of "X" with different types.

    Thanks,
    John

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup Criteria (XL97; SR2)

    You could use an array formula like this:

    =SUM(IF((A2:A4="X")*(C2:C4=21);D24;""))

    which should be entered using ctrl+shift+enter. This formula will return 75.

    Here I assumed your table in A14.

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VLookup Criteria (XL97; SR2)

    If your table is in cells A1 to D4 the following formula will work. The values that you are looking up are assumed to be:
    E1 is input value for Unit (eg X)
    F1 is input value for Type (eg 14)

    =INDEX(D24,MATCH(E1&F1,A2:A4&C2:C4,0))

    This is an array formula so you need to use ctrl+shift+enter to enter the formula.

Posting Permissions

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