Double Vertical Index & Match

[Qaspec]

Sheet1
A B C
1 Date Time Score
2 3/1/08 0000 91
3 3/1/08 0600 96
4 3/2/08 0000 95
5 3/2/08 0600 97

Sheet2
A14 = 0000
B12 = 3/2/08

Times and scores can be duplicated and date and scores can be duplicated to
i need to validate by date and time which were created seperately by another
system. i'd like to do this without creating an extra or hidden columns.

I would like a formula to return the scrore 95 from sheet 1 that corresponds
to

 

[PCLIVE]

If there will only be one match, or if you'll want to sum the C values if
there are multiple matches, then you could use SUMPRODUCT.

=SUMPRODUCT(--(A2:A5=Sheet2!A15),--(B2:B5=Sheet2!A14),C2:C5)

HTH,
Paul

 

[Qaspec]

I'm using the following and I am getting a return of 0. is there something
wrong with the formula the way it is entered?

=SUMPRODUCT(--(data!A4:A12=B12),--(data!B4:B12=A14),data!C4:C12)

 

[PCLIVE]

It may have something to do with what sheet you are placing this formula.
Try adding the Sheet2 refererence.

=SUMPRODUCT(--(data!A4:A12=Sheet2!B12),--(data!B4:B12=Sheet2!A14),data!C4:C12)

Also, you may need to verify that the format of your data in column A
matches the format of the criteria in B12. Same thing goes with column B
and A14.

Regards,
Paul



--

 

[Qaspec]

Still showing 0,

=SUMPRODUCT(--(data!A4:A12=CRC!B12),--(data!B4:B12=CRC!A14),data!C4:C12)

 

[PCLIVE]

Trying Copying cell B12 which contains the date criteria and paste it in A6
of your data (which should be the same). This is to test if your criteria
is actually matching your data. Press F9 to recalculate and see if your
formula shows the correct figure. If not, then copy cell A14 which appears
to have the text "0000" in it, and paste it in cell B6 of your data.

Note: I'm assuming A6 and B6 is where this falls within your sample data
since your ranges start at row 4.

As mentioned before, I'm still thinking that your criteria is actually
matching to the data in your range, even though it may appear to be the
same.

HTH,
Paul


--

 

[Qaspec]

Actually the data was being stored as text in column c, once i changed it to
number it worked. Thank you for your help PCLIVE.

 

[PCLIVE]

Stupid me. I should have thought to mention that. Anyway, I'm glad it's
working for you now.

Regards,
Paul

--