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’.
{=ADDRESS(MIN(IF(tabref=SearchValue,ROW(tabref))),MIN(IF(tabref=SearchValue,COLUMN(tabref))))}
| A | B | C |
| 1 | 5 | 9 |
| 2 | 6 | 10 |
| 3 | 7 | 11 |
| 4 | 8 | 12 |
If the above table is what we have in Excel, then the above Array formula would return the value as ‘$A$2’.
Also Read: Remove Duplicate Values from Excel Sheet
Why we Need Array Formula? Is VLOOKUP not enough?
A Lookup function can only search for first row or column for search value in a table. Then it returns the column or cell value as mentioned in the parameter. If we need to search for other row or column, then we can even use INDEX and MATCH Functions.
But these formulas also have to be used recursively, if not used as an Array Formula. These functions are mostly used by Power users, since it is bit of complex to understand how they work and does not give desired output if not properly used.