Excel Write cell if another cell has a 1 [closed]

Question

I have done some digging and I haven't found the solution to my problem. To be honest I don't know the "keywords' to search to find the solution to this problem.

I have a list of names in a in one sheet2.

I want to scan a column with that person's name at the top in sheet1. every time the number "1" appears in that column I want to write the text (Course name) from that row. The number 1 will appear up to 7 times.

I have tried vertical lookup and if statements but not getting what I need.

A picture is worth a thousand words I guess.Sheet1 Sheet 2

Answer 1

Assuming your data are in the cells below on Sheet1 (it's impossible to be sure between your having merged cells, and no row labels visible):

I believe this is what you want for Sheet2:

The formula in cells C2:C6 of Sheet2 is

=TRANSPOSE(filter(Sheet1!$B$8:$B$16,INDEX(Sheet1!$I$8:$K$16,,MATCH(B2,Sheet1!$I$4:$K$4,0))))

The formula below

=filter(B8:B16,I8:I16)

if entered on Sheet1 would result in a vertical range comprising BIOLOGIE 11, and BUSINESS TECHNOLOGY 11, i.e. entries from B8:B16 where the rows have a 1 in the I8:I16 range. Since there are several students, the I8:I16 range must be determined dynamically; this is why the INDEX() and MATCH() functions are used. The INDEX() function is used to a return a component of the range used as it's first argument, i.e. part of the Sheet1!$I$8:$K$16 range, as that is where the 1s/blanks for the 3 (visible) students reside (you will have to widen it for your actual data).

The INDEX() function can take 2 additional arguments, being the row number and column number of the cell (within Sheet1!$I$8:$K$16) that we want returned. While both of these arguments can be supplied, only one is mandatory - in this context, the row argument is skipped, and only a value for the column is supplied: this means that the component of Sheet1!$I$8:$K$16 that we want return is an entire column (i.e. all the rows for that column).

The MATCH() function is what is used to determine the column, i.e. it identifies the position, of the current student's name, within the Sheet1!$I$4:$K$4 range (the 3rd argument, 0, to the MATCH() function means that we want an exact match; again you will have to widen this range for your actual data). In this way the INDEX() and MATCH() functions combined identify the column of 1s/blanks appropriate to the current student; this then forms the second argument to the FILTER() function, so that it can return the subjects appropriate to the current student.

Finally, the TRANSPOSE() function is used because you want the results populated horizontally whereas, by default, the FILTER() function returns a vertical range.