Return value based on other cells

[angeloudaki]

Hi! I thought I had this - turns out I am rubbish.

I basically want to return a value in N3, if F3 (text data input) contains a value in N2 (header), otherwise return 0

I currently have : =COUNTIF($F3,"*"&N$2&"*") which works great to return a numerical value, but i want to later get a string of letters summarising the content instead of numbers...

e.g.:

F

G

H

I

J

K

L

M

N

O

P

Q

2​

Accessories issued

Due

provision via

Status

Note

Bag?

issue

ID

Apple

Pear

Banana

Grape

3​

Apple, Pear, Banana

TBC

TBC

PENDING

A01

A

P

B

0

4​

Apple, Banana

TBC

TBC

PENDING

C05

A

0

B

0

5​

Grape, Banana

TBC

TBC

PENDING

D25

0

0

B

G

Obviously my diet isn't this healthy. Can't remember the last time I had an apple haha...

 

[Fluff]

Maybe

=IF(COUNTIF($F3,"*"&N$2&"*"),LEFT(N$2),0)

 

[angeloudaki]

=IF(COUNTIF($F3,"*"&N$2&"*"),LEFT(N$2),0)

Thank you! Sadly though, this just returns 0 Does 'Left' indicate the leftmost character? Would be fab if that worked!

 

[Fluff]

Does 'Left' indicate the leftmost character?

Yes it does.
That formula works for me

+Fluff 1.xlsm
	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	Accessories issued	Due	provision via	Status	Note	Bag?	issue	ID	Apple	Pear	Banana	Grape
3	Apple, Pear, Banana	TBC	TBC	PENDING				A01	A	P	B	0
4	Apple, Banana	TBC	TBC	PENDING				C05	A	0	B	0
5	Grape, Banana	TBC	TBC	PENDING				D25	0	0	B	G
Data

Cell Formulas
Range	Formula
N3:Q5	N3	=IF(COUNTIF($F3,"*"&N$2&"*"),LEFT(N$2),0)

 

[angeloudaki]

=IF(COUNTIF($F3,"*"&N$2&"*"),LEFT(N$2),0)

Ahaa that one did work... thank you so much! I don't understand why copy paste didn't work the first time haha. Worked this time I clearly need to practice my CTRL+C skillz!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[angeloudaki]

You're welcome & thanks for the feedback.

Sorry - another question (based upon the result) - how do I change that bit ....,LEFT(N$2),0) ..... to include the first two letters of a cell? Or do I just need to add the letters in " "?

 

[Fluff]

How about

=IF(COUNTIF($F3,"*"&N$2&"*"),LEFT(N$2,2),0)

 

[angeloudaki]

How about

=IF(COUNTIF($F3,"*"&N$2&"*"),LEFT(N$2,2),0)

You are AMAZING!!! Thank you! <- how i feel right now

 

[Fluff]

Glad to help & thanks for the feedback.