Look value above 0 on E and return A

[douglascaixeta]

I would like to look at every value that is greater than zero in column E, and if its greater than zero, should return the value on column A on the same line.
But there are more than one line with values greater than 0, and should return every single name on column A, not just the first or the bigger.

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[douglascaixeta]

The version is Office 2019.

 

[Fluff]

And the data sample?

 

[douglascaixeta]

Good Company.xlsx
	A	B	C	D	E
19		Bananas	Apples	Pineapple	Strawberries
20	A	4	2	0	4
21	B	0	2	0	0
22	C	0	0	25	0
23	D	0	0	0	0
24	E	0	0	0	0
25	F	0	0	0	0
26	G	0	0	5	3
27	H	0	0	0	0
28	I	0	0	0	0
29	J	0	0	0	0
30	K	0	0	0	0
31	L	0	0	0	0
32	M	0	0	0	0
Recipes Table

Would like to show A and J and respective numbers 4 and 3 if I select Strawberries.

 

[Fluff]

You still haven't shown you expected results. Do you want it in one cell, against each row or what?

 

[douglascaixeta]

I would like to look at every value that is greater than zero in column E, and if its greater than zero, should return the value on column A on the same line.
If I do a simple formula it will return the zeros. The expected result if I select Strawberries is:
A 4
G 3

 

[Fluff]

Maybe

+Fluff 1.xlsm
	A	B	C	D	E	F	G
19		Bananas	Apples	Pineapple	Strawberries
20	A	4	2	0	4		A4,G3
21	B	0	2	0	0
22	C	0	0	25	0
23	D	0	0	0	0
24	E	0	0	0	0
25	F	0	0	0	0
26	G	0	0	5	3
27	H	0	0	0	0
28	I	0	0	0	0
29	J	0	0	0	0
30	K	0	0	0	0
31	L	0	0	0	0
32	M	0	0	0	0
Main

Cell Formulas
Range	Formula
G20	G20	=TEXTJOIN(",",1,IF(E20:E32>0,A20:A32&E20:E32,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Please don't forget to update your profile to show which version/platform you are using.

 

[douglascaixeta]

Ok that is good. I wasn't looking to put all info on the same cell, but that works fine.

Can we make a way to select the name and the formula will search for the correct column rather than manually select the column? This way I can type the name and it will give me the values, instead of having to manually select the correct column.

Like this: *But having on the same cell is ok too.

Bananas	A4
Apples	A2	B2
Pineapple	C25	G5
Strawberries	A4	G3

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
19		Bananas	Apples	Pineapple	Strawberries
20	A	4	2	0	4		Bananas	A4
21	B	0	2	0	0		Apples	A2, B2
22	C	0	0	25	0		Pineapple	C25, G5
23	D	0	0	0	0		Strawberries	A4, G3
24	E	0	0	0	0
25	F	0	0	0	0
26	G	0	0	5	3
27	H	0	0	0	0
28	I	0	0	0	0
29	J	0	0	0	0
30	K	0	0	0	0
31	L	0	0	0	0
32	M	0	0	0	0
33
34
Main

Cell Formulas
Range	Formula
H20:H23	H20	=TEXTJOIN(", ",,IF(($B$19:$E$19=$G20)*($B$20:$E$32>0),$A$20:$A$32&INDEX($B$20:$E$32,,MATCH(G20,$B$19:$E$19,0)),""))
Press CTRL+SHIFT+ENTER to enter array formulas.