Pickup Value

[Jonathan Lee]

Dear Sifus,

How can I pick up desired value in a range of cells?

Example:

Column A Column B Column C
Basket 1 Apple
Basket 1 Apple
Basket 1 Orange
Basket 2 Apple
Basket 2 Apple
Basket 2 Apple

Needs to return the value in column C:

Basket 1 Orange
Basket 2 Apple

Rule is, as long as there are oranges in the basket, returns orange. Else Apple.

Note, there could be other fruits as well but I am only taking oranges and apples. If there are other fruits, other than oranges and apples, returns apple.

Thanks for your help.

 

[Peter_SSs]

Like this?

21 10 19.xlsm
	A	B	C
1
2	Basket 1	Apple	Orange
3	Basket 1	Apple	Orange
4	Basket 1	Orange	Orange
5	Basket 2	Apple	Apple
6	Basket 2	Apple	Apple
7	Basket 2	Apple	Apple
Apples

Cell Formulas
Range	Formula
C2:C7	C2	=IF(COUNTIFS(A$2:A$7,A2,B$2:B$7,"Orange"),"Orange","Apple")

 

[Jonathan Lee]

Like this?

21 10 19.xlsm
	A	B	C
1
2	Basket 1	Apple	Orange
3	Basket 1	Apple	Orange
4	Basket 1	Orange	Orange
5	Basket 2	Apple	Apple
6	Basket 2	Apple	Apple
7	Basket 2	Apple	Apple
Apples

Cell Formulas
Range	Formula
C2:C7	C2	=IF(COUNTIFS(A$2:A$7,A2,B$2:B$7,"Orange"),"Orange","Apple")

Thanks for the prompt reply.

I have another scenario.

I need to return a blank if the basket is empty. Please advise. Thanks in advance.

 

[Peter_SSs]

I need to return a blank if the basket is empty.

Like this? (If not, sample data and expected results please)

21 10 19.xlsm
	A	B	C
1
2	Basket 1	Apple	Orange
3	Basket 1	Apple	Orange
4	Basket 1	Orange	Orange
5	Basket 2	Apple	Apple
6	Basket 2	Apple	Apple
7	Basket 2	Apple	Apple
8	Basket 3
9	Basket 3
10	Basket 3
Apples (2)

Cell Formulas
Range	Formula
C2:C10	C2	=IF(COUNTIFS(A$2:A$10,A2,B$2:B$10,"Orange"),"Orange",IF(COUNTIFS(A$2:A$10,A2,B$2:B$10,"?*"),"Apple",""))

 

[Jonathan Lee]

Like this? (If not, sample data and expected results please)

21 10 19.xlsm
	A	B	C
1
2	Basket 1	Apple	Orange
3	Basket 1	Apple	Orange
4	Basket 1	Orange	Orange
5	Basket 2	Apple	Apple
6	Basket 2	Apple	Apple
7	Basket 2	Apple	Apple
8	Basket 3
9	Basket 3
10	Basket 3
Apples (2)

Cell Formulas
Range	Formula
C2:C10	C2	=IF(COUNTIFS(A$2:A$10,A2,B$2:B$10,"Orange"),"Orange",IF(COUNTIFS(A$2:A$10,A2,B$2:B$10,"?*"),"Apple",""))

Thanks… it works

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.