Vlookup using multiple conditions

[Charlene Hainsworth]

I have a VLOOKUP formula that returns the rate for a piece of equipment based on the unit# entered. Both my lookup vector and result vector are on sheet 2. My formula works great so far but I want to add a second criteria and can't figure out how.

Sheet 1 is set up as follows:
Column 1 Column 2 Column 3
Unit# Unit of Measure Rate
676 Hour 15


Sheet 2 is set up as follows:
Column 1 Column 2 Column 3
Unit# Unit of Measure Rate
676 Hour 15
676 Day 100

My formula in Sheet 1 cell C2 reads:

=LOOKUP(A2,'Sheet2'!$A$2:$A$100,'Sheet2'!$C$2:$C$100)

How do I add a second condition to my formula that will change my rate to $100 if Day is selected on Sheet 1 instead of Hour?

[ChemistB]

This is an Array formula. When you enter it, use CNTRL SHFT ENTER instead of ENTER. You'll see brackets appear around the formula. Then copy down

=INDEX(Sheet2!$C$2:$C$100,MATCH(A2&B2,Sheet2!$A$2:$A$100&Sheet2!$B$2:$B$100,0))
Does that work for you?

[tigeravatar]

Alternative:
=SUMPRODUCT(--(Sheet2!$A$2:$A$100=A2),--(Sheet2!$B$2:$B$100=B2),Sheet2!$C$2:$C$100)

[Charlene Hainsworth]

This worked superb! Thanks soooo much!