Vlookup & 2 variables

[Marti]

Is it possible to use vlookup for 2 criteria? I want to go to another tab in
my workbook and lookup the value that meets 2 criteria. in the example below,
I need the id# and project id # to match values on the other tab and return
the $ value that matches both.

ID # Project ID Amount
123 0523 $1,000
456 0645 $2,000
789 0752 $4,000
028 0523 $5,000

 

[T. Valko]

I'm assuming all your data with leading 0s is formatted as text.

Try this:

A1 = 028
B1 = 0523

=SUMPRODUCT(--(Sheet2!A1:A10=A1),--(Sheet2!B1:B10=B1),Sheet2!C1:C10)

 

[Marti]

I tried it, but it's not working. the data is all numbers, so I tried the
formula as written below and w/o the -- but still getting an error message

 

[T. Valko]

but still getting an error message

You tried this formula and got an error message?

=SUMPRODUCT(--(Sheet2!A1:A10=A1),--(Sheet2!B1:B10=B1),Sheet2!C1:C10)

What error message?

Try this array formula** :

A1 = lookup ID #
B1 = Project ID

=INDEX(Sheet2!C1:C10,MATCH(1,(Sheet2!A1:A10=A1)*(Sheet2!B1:B10=B1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.