Vlookup & 2 variables
 

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

Vlookup & 2 variables
 

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)

--
Biff
Microsoft Excel MVP


"Marti" wrote in message
...
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

Vlookup & 2 variables
 

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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Marti" wrote in message
...
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

Vlookup & 2 variables
 

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)*(S heet2!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.

--
Biff
Microsoft Excel MVP


"Marti" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Marti" wrote in message
...
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