I have a string in the cell A1 in sheet name Sheet 1. I now use the MATCH function to find the string of cell A1 in the range A1:Z1 of a different sheet. That works fine so far. The function returns the column number. (let's say 5, in column E)

My overall goal is to determine how many non-empty cells I have in a certain column. For that, I can use the COUNTA formula which expects a range as parameter. My problem is that I do not know how to convert the number of a column into a valid range without using VBA.

Manually I would do COUNTA(E:E) but I need to create a range for a numbered column like 5.

Thx.

CodePudding user response：

Use INDEX:

=COUNTA(INDEX(Sheet2!A:Z,,MATCH(A1,Sheet2!A1:Z1,0)))