In this article, I will tell you how to use the excel **INDEX** & **MATCH** function to find the first non-empty cell in a row or column, it will also tell you how to use the excel **LOOKUP**Â function to find the last non-empty cell in a row or column with examples.

### 1. How To Use Excel INDEX & MATCH Function To Find The First Non-Empty Cell In A Row Or Column.

- Below is cthis article’s example’s source cell data.

- If you want to get the first non-empty cell in column
**A**, you can use the below formula.=INDEX(A1:A10,MATCH(TRUE,INDEX((A1:A10<>0),0),0))

- Input the above formula into cell
**C1**and press the enter key, then it will show the text**iPhone**in cell**C1**. - Because the text
**iPhone**is the first non-empty cell’s text in column**A**. - The formula uses both excel
**INDEX**and**MATCH**functions, now I will explain the formula to you.

#### 1.1 (A1:A10<>0).

- The
**(A1:A10<>0)**part in the formula will return a boolean list, and each element in the list will be**TRUE**or**FALSE** - It will compare each cell value in the cell range
**A1:A10**to the value**0**, if the cell value is not**0**( or**not empty**) then return**TRUE**, if the cell value is 0 ( or**empty**) then return**FALSE**. - If you input the formula
**=(A1:A10<>0)**in cell**C1**and press the enter key, then you can get the below column in cell range**C1:C10**.

#### 1.2 INDEX((A1:A10<>0),0).

- The excel
**INDEX**function is used to find and return the value in a specified cell range located by provided row and column number. You can read the article How To Use Excel Index Function With Examples to learn how to use it. - So the
**INDEX((A1:A10<>0),0)**part will return the first column in the boolean list generated by the**(A1:A10<>0)**part. - So when you input the formula
**=INDEX((A1:A10<>0),0)**in cell**C1**, it will show the below boolean column in cell range**C1:C10**also.

#### 1.3 MATCH(TRUE,INDEX((A1:A10<>0),0),0).

- The excel
**MATCH**function will return the first element’s position number that matches the search value in the value list, you can read the article How To Use Excel Match Function With Examples to learn more. - In this formula, it will find the first cell’s position number that value is
**TRUE**in the value list generated by the formula**INDEX((A1:A10<>0),0)**. - Input the formula
**=MATCH(TRUE, INDEX((A1:A10<>0),0),0)**in cell**C1**and press the enter key, then it will show the number**2**in cell**C1**. - Passing
**0**to the third argument of the**MATCH**function means an exact match. - And because the second cell’s ( cell
**A2**) value is not empty, then it returns the cell**A2**‘s position number which is number**2**.

#### 1.4 INDEX(A1:A10,MATCH(TRUE,INDEX((A1:A10<>0),0),0),1).

- This formula
**=INDEX(A1:A10, MATCH(TRUE, INDEX((A1:A10<>0),0),0),1)**can be translated to**=INDEX(A1:A10,2,1)**then it will return the second cell ( cell**A2**) ‘s value (**iPhone**) in the cell.

### 2. How To Use Excel LOOKUP Function To Find The Last Non-Empty Cell In A Row Or Column.

- Now we will use the excel
**LOOKUP**function to find the last non-empty cell in a row or column. - We still use the above example data cells.

- Input the formula
**=LOOKUP(2,1/(NOT(ISBLANK(A1:A10))), A1:A10)**in cell**C1**and press the enter key, then it will show the text**Apple Watch**( the last non-empty cell**A9**‘s value ) in cell**C1**. - Now I will explain the formula to you part by part.
- Input the formula
**=A1:A10Â**in cell C1 then it will display the value list as below.

- Input the formula
**=ISBLANK(A1:A10)**in cell**C1**then it will show the below value list.

- And the formula
**=NOT(ISBLANK(A1:A10))**will return the below value list.

- The formula
**=1/(NOT(ISBLANK(A1:A10)))**will return the below value list. If the cell value is empty then the formula**1/(NOT(ISBLANK(A1:A10)))**returns the error**#DIV/0!**

- Now the formula
**=LOOKUP(2,1/(NOT(ISBLANK(A1:A10))), A1:A10)**will be translated to**=LOOKUP(2,{‘#DIV/0!’, 1, ‘#DIV/0!’, 1 , 1, 1, ‘#DIV/0!’, 1, 1, ‘#DIV/0!’}, A1:A10)**. - The excel lookup function will look for the first parameter value
**2**in the second array list from the first array element to the last array element. - If it can not find the number value
**2**until the last element in the second array list, it will return the last non-empty array element’s row number which is row**9**. - Then the
**LOOKUP**function will return the number**9th**element (**A9**) value (**Apple Watch**) in the third parameter (**A1:A10**). - Besides the above formula, you can also use the formula
**=LOOKUP(0.1,0/(NOT(ISBLANK(A1:A10))), A1:A10)**to get the last non-empty element, the principle is the same as the first formula. - If you want to get the last non-empty cell’s row number in this example.
- You can use the formula
**=LOOKUP(2,1/(A1:A10<>””),ROW(A1:A10))**to implement it. - You can read the article Excel Table LOOKUP Function Examples to learn how to use the excel
**LOOKUP**function.

Andy MayesSadly does not work for a row.

JerryCan you give me detailed information about what can not work? Thanks a lot.