Double Vertical Index & Match

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Qaspec - 26 Mar 2008 21:36 GMT

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

Reply to this Message

PCLIVE - 26 Mar 2008 22:00 GMT

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

> Sheet1
> A B C
[quoted text clipped - 17 lines]
> corresponds
> to

Reply to this Message

Qaspec - 27 Mar 2008 14:10 GMT

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)

> 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.
[quoted text clipped - 25 lines]
> > corresponds
> > to

Reply to this Message

PCLIVE - 27 Mar 2008 14:49 GMT

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

> 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?
[quoted text clipped - 32 lines]
>> > corresponds
>> > to

Reply to this Message

Qaspec - 27 Mar 2008 15:08 GMT

Still showing 0,

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

> It may have something to do with what sheet you are placing this formula.
> Try adding the Sheet2 refererence.
[quoted text clipped - 44 lines]
> >> > corresponds
> >> > to

Reply to this Message

PCLIVE - 27 Mar 2008 15:42 GMT

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

> Still showing 0,
>
[quoted text clipped - 50 lines]
>> >> > corresponds
>> >> > to

Reply to this Message

Qaspec - 27 Mar 2008 16:19 GMT

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.

> 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
[quoted text clipped - 66 lines]
> >> >> > corresponds
> >> >> > to

Reply to this Message

PCLIVE - 27 Mar 2008 16:28 GMT

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

Regards,
Paul

> Actually the data was being stored as text in column c, once i changed it
> to
[quoted text clipped - 77 lines]
>> >> >> > corresponds
>> >> >> > to

Reply to this Message