Last Row of SUMPRODUCT

[jerm_e]

I've been everywhere online I can think of to no avail. I'm attempting to return only the last value of a sumproduct's result. (read: last row).

For example:

=SUMPRODUCT(--($C$2=BCBS!A:A),--("_"=BCBS!B:B),--(BCBS!C:C=MAX(BCBS!C:C)),BCBS!E:E)

This works for values that match A and B that have multiple results in C (it returns the value in E for the highest row number). However, it does not work with values that match A and B that only have a single result in C.

Any ideas on this approach or another?

[NBVC]

Maybe you want:

=LOOKUP(2,1/(($C$2=BCBS!A:A)*("_"=BCBS!B:B)*(BCBS!C:C=MAX(BCBS!C:C))),BCBS!E:E)

You should try not to use whole column references with these types of formulas as they are slow to process....

[jerm_e]

Number 1: Thank you for replying. I had zero luck elsewhere in trying to garner some sort of idea/response.

Number 2: Your formula is picking up the correct values when there are multiple values in Column C, but when there is only a single result (i.e. the original sumproduct only has one row to look at), it still returns #N/A.

[NBVC]

I am not quite sure I understand. Can you give example of what you mean by there is only a single result and it giving you #N/A error?

[jerm_e]

Sheet BCBS Contains The following Information:

A..................B.............C......................D................................................E

86780............_ ............7/1/2011............TREPONEMA PALLIDUM............20
90732............_ ............7/15/2011..........PNEUMOCOCCAL VACCINE.........2
90732............_ ............10/15/2011........PNEUMOCOCCAL VACCINE......64.75

$C$2 on the CALC/home sheet contains a 5 digit number to lookup. If I lookup 90732 I return 64.75 (correct). When I lookup 86780 I return #N/A (incorrect). Does this help you? I can give you more info if needed.

[NBVC]

If your looking for the last, maybe we don't need to look for max...

i.e.

=LOOKUP(2,1/(($C$2=BCBS!A:A)*("_"=BCBS!B:B)),BCBS!E:E)

[jerm_e]

That seems to be doing the trick! Will this handle things if there are 3, 4, or 5 additional occurrences of 90732? i.e. Will it always return the final row's value?

[Haseeb Avarakkan]

Try this,

=LOOKUP(9E300,SEARCH(C2,BCBS!A2:A30)*SEARCH("_",BCBS!B2:B30),BCBS!E2:E30)

[NBVC]

Yes it should.

Lookup(2,1/.....) looks for last occurance of the combined conditions....

[jerm_e]

Amazing. After 4 hours research, I find someone who can show it, do it, and teach it in 4 minutes. Thank you for your help.

[NBVC]

You are welcome

Remember that whole column usage in this formula will slow down the spreadsheet processing.. Try using a defined range.

[Azam Ali]

also try
=MAX(INDEX(((BCBS!A1:A10=$C$2)*(BCBS!$B$1:$B$10="_")*BCBS!E1:E10),0))

[NBVC]

That will pick up the max value in column E with the combined conditions, not necessarily the last row containing the combined conditions.

[jerm_e]

I'll define some ranges to make things run a bit better. It is a little clunky, but quite acceptable for its current use. Thanks again!