Vlookup & 2 variables

M

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

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)
 
M

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top