lookup value conditionally in 3 columns

[CAABYYC]

Hi, could you please help to answer following question regarding value lookup ? (please use function and formula only) thanks !!!

1. the maximum value of item A in 2014 ( 318)
2. the minimum value of item B in 2014 ( 2)


Source data: A1:C15 (including header)
For the same item, the Value doesn't have duplicate number.

ITEM YEAR VALUE
A 2013 10
A 2014 1
A 2014 115
A 2014 215
A 2014 21
B 2014 11
B 2014 3
B 2014 34
B 2014 2
C 2014 3
C 2014 8
C 2014 6
A 2014 318
A 2014 12

[JohnTopley]

Try

=MAX(IF(($A$2:$A$15="A")*($B$2:$B$15=2014),$C$2:$C$15))

=MIN(IF(($A$2:$A$15="B")*($B$2:$B$15=2014),$C$2:$C$15))

Enter both with Ctrl+Shift+Enter

[CAABYYC]

Thanks, John, except the value, I also want to see the "row#" of matched value. example, if I want to use a helper column at the right side of source data.

1. the maximum value of item A in 2014 ( 318) --- row (14)
2. the minimum value of item B in 2014 ( 2) ----row(12)

[JohnTopley]

=MATCH(LARGE(IF(($A$2:$A$15="A")*($B$2:$B$15=2014),$C$2:$C$15),1),$C$2:$C$15,0)+1

=MATCH(SMALL(IF(($A$2:$A$15="B")*($B$2:$B$15=2014),$C$2:$C$15),1),C2:C15,0)+1

Entered with Ctrl+Shift+Enter

[JohnTopley]

be better

=MATCH(LARGE(IF(($A$1:$A$15="A")*($B$1:$B$15=2014),$C$1:$C$15),1),$C$1:$C$15,0)

=MATCH(SMALL(IF(($A$1:$A$15="B")*($B$1:$B$15=2014),$C$1:$C$15),1),$C$1:$C$15,0)

[CAABYYC]

Thank you so much, John

[CAABYYC]

Hi,John,
after quick test, minor error was found in your formula to get row#.

as what I said in the note for example database, "For the same item, the Value doesn't have duplicate number." that means same value# could be found in column C but this was not shown in example.
if you change the "11" to "318" in "B 2014 11" at row 7, following 2 formula will get the row# for B instead of A. could you pls take a look again??


=MATCH(LARGE(IF(($A$2:$A$15="A")*($B$2:$B$15=2014),$C$2:$C$15),1),$C$2:$C$15,0)+1
=MATCH(LARGE(IF(($A$1:$A$15="A")*($B$1:$B$15=2014),$C$1:$C$15),1),$C$1:$C$15,0)

[bebo021999]

Use these array formula:

Please Login or Register  to view this content.

Change MAX to MIN

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[CAABYYC]

that works and thanks

cảm ơn bạn đã giúp đỡ

Use these array formula:

Please Login or Register  to view this content.

Change MAX to MIN

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[bebo021999]

You are welcome.