1. 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. 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. 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
•