Need To Lookup Value Based on Two Criteria

[Monte Sliger]

Hi,

I need a formula to return a value from a row in a different worksheet
within the same workbook. The row must meet a combination of two different
criteria. I previously used vlookup based on the REF column (see example
below) to return the value in the Total column for that row, but now I am
finding that the values in the REF column are starting to repeat. Here is
an example:

Sheet1

Date Name Ref Total
08/15/03 XYZ 325 100
08/15/03 def 276 220
02/12/04 abc 396 250
07/02/04 def 325 275

Date column is formatted as date; Name & Ref columns formatted as text;
Total column formatted as number. Data in any column may be duplicated on
multiple rows, but combination of Date and Ref will always be unique.

Sheet2

Cell A1=325 (formatted as text)
Cell B1=07/02/04 (formatted as date)

Need Formula in Sheet2, Cell A2 to return the value in the total column for
the one row which has the data in cells A1 & B1 (in this case 275). Again,
the combination of A1 and B1 will always be unique.

I am using Excel 2000 if that makes any difference.

Thanks for all help.


Monte

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX('sheet2'!$D$1:$D$100,MATCH(1,('sheet1'!$A$1:$A$100=B1)*('sheet1'
!$C$1:$C$100=A1),0))

 

[Domenic]

Hi,

=INDEX(Sheet1!$D$2:$D$5,MATCH(1,(Sheet1!$A$2:$A$5=Sheet2!B1)*(Sheet1!$C$2:$C$5=Sheet2!A1),0))

entered using CTRL+SHIFT+ENTER

adjust the range according to your data

Hope this helps!