## Excel Functions Vs Array Formula

In this article, we are going to search for value in a Excel table without using Vlookup function.

The formula that we are going to use are categorized as Array formula. The difference is simple. With normal Excel formula, we will enter a formula like ‘=Sum(1+2)’ and press enter to get result as ‘3’.

With these special Array formula, we should not press enter. We should actually press ‘Control + Shift + Enter’ keys to tell Excel to calculate the formula. Lets see how it is done with an simple example.

## How to get Row and Column number of a Value in a Table?

Assuming a Excel table with range is A1:E10, all with numbers in it and we need to search for a number, in this case ‘2’. Then enter the Array formula** to get the Row or Column number.

{=MIN(IF(A1:E10=2,ROW(A1:E10)))}

{=MIN(IF(A1:E10=2,COLUMN(A1:E10)))}

**Note: This is not a usual Excel formula. If you give enter after typing the formula it will give #Value error. Press Ctrl + Shirt + Enter to get the actual result from an Array Formula.

With an Array Function it is possible to get more than 1 value as result. So, we use MIN or MAX function according to situation to restrict the result to 1. This kind of formula executes the operation in every cell that is mentioned in the range and returns the value. This way we avoid the need for writing a Excel Macro.

The above two formula can be enhanced to get the address of specific cell which has the value we are searching for.

## How to get address of cell that has search value in Table?

Use this formula in a cell and press Ctrl + Shift + Enter to get the address of search value. Consider tabref is range ‘A1:E10’ and SearchValue = ‘2’.