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 this article’s example’s source cell data.

**A****B****C****D****E****1****2**iPhone 1000 **3****4**iPad 900 **5**Macbook Pro 1500 **6**Macbook Air 1800 **7****8**Mac Mini 1000 **9**Apple Watch 800 **10** - 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**.

FALSE TRUE FALSE TRUE TRUE TRUE FALSE TRUE TRUE FALSE

#### 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.

FALSE TRUE FALSE TRUE TRUE TRUE FALSE TRUE TRUE FALSE

#### 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.

**A****B****C****D****E****1****2**iPhone 1000 **3****4**iPad 900 **5**Macbook Pro 1500 **6**Macbook Air 1800 **7****8**Mac Mini 1000 **9**Apple Watch 800 **10** - 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.

0 iPhone 0 iPad Macbook Pro Macbook Air 0 Mac Mini Apple Watch 0 - Input the formula
**=ISBLANK(A1:A10)**in cell**C1**then it will show the below value list.

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

FALSE TRUE FALSE TRUE TRUE TRUE FALSE TRUE TRUE FALSE - 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!**

#DIV/0! 1 #DIV/0! 1 1 1 #DIV/0! 1 1 #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.

Sadly does not work for a row.

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