# Thread: Get value if "1" Thanks: 0 Likes: 0

1. ## Get value if "1"

Hi,
How can i get the first line value of the collumn with "1" value and print the result on the last column like in example above:
HTML Code:
`<img src="https://i.ibb.co/R9WBhzq/Capturar.png" alt="Capturar" border="0">`
Thank you very much.

2. ## Re: Get value if "1"

Try

=INDEX(A1:G2,1,MATCH(1,A2:G2,0))&","&INDEX(A1:G1,1,MAX(IF(A2:G2=1,COLUMN(A2:G2))))
Array formula, use Ctrl-Shift-Enter

3. ## Re: Get value if "1"

Or non array formula

=INDEX(A1:G2,1,MATCH(1,A2:G2,0))&","&INDEX(A1:G1,1,AGGREGATE(14,6,(A2:G2=1)*COLUMN(A2:G2),1))

4. ## Re: Get value if "1"

Your output is incorrect in I2, it should be Titulo3, Titulo5

Also, I made a mistake in the formulas

in I2
=INDEX(A\$1:G\$1,1,MATCH(1,A2:G2,0))&","&INDEX(A\$1:G\$1,1,AGGREGATE(14,6,(A2:G2=1)*COLUMN(A2:G2),1))
and copy down the column

5. ## Re: Get value if "1"

it works, awesome!!
But i have a problem, in the row number 5 i have more than two "1".

printscreen:

Thank you

6. ## Re: Get value if "1"

This is turning from a "how can i get the first and last non zeros in a row" to
"How can I concatenate all the non zeros in a row"
which is much more complicated process.

Whats your expected output for that row?

Title1,2,3,4,5,7 ?

If so then unless you have TEXTJOIN, you would need VBA to do this and not formulas (unless you know there will only be 7 columns).

Will there only be 7 columns? Or more?

7. ## Re: Get value if "1"

Yes, that's the output i want.
will be more than 7 columns, around 40

8. ## Re: Get value if "1"

Formulas are not good for this, suggest you use VBA, I'm not an expert in that I'm afraid

9. ## Re: Get value if "1"

i found this example https://stackoverflow.com/questions/...mn-below-has-1
i think is this way.

Thank you for your help . have a good day!!