Today, we are going to discuss about the advanced level of details in INDEX function.
In our blog, we used INDEX in coordination with MATCH, SMALL, AGGREGATE and even with COUNTIF in numerous occasions to extract the data.
The character of the function what we are going to discuss here, makes this function to stand alone or with very few other functions.
Let me give you the simple example of lookup using VLOOKUP and INDEX to differentiate the ability of the functions.
Here, we do have the simple data set, Month in rows and week across columns. In the cell I7 we did simple two way lookup using VLOOKUP function, In the cell I13, we accomplished the same with INDEX function as shown below,
We are not going to bother about, which one is fast or effective, 2 match lookup in INDEX or 1 match lookup in VLOOKUP.
Our at most concern is to check the character of the output of each function.
The output of VLOOKUP function in I7 cell is the cell value 15 which is the intersection of the month Aug and Week 4. Let me repeat again, Output is the cell value.
But, the output of INDEX function in I13 cell is the range. Intersection range of the month Aug and Week 4 is range F11. So the INDEX function delivers F11 and then after evaluation it gives the cell value of the range.
Let me prove that with the below example,
In the cell I13, I wrapped the INDEX function inside the CELL function. As the first argument I picked "address" from the drop down, for the reference argument in CELL function, INDEX function is fed. The output looks like below,
Output of INDEX function accepted by the reference argument of cell function and it delivered the address of that reference based on the selected option.
I did the same for VLOOKUP function and the result shown below,
When I press enter, excel not able to understand our requirement and throws below error,
Since the output of VLOOKUP function is the cell value 15, CELL function not able to find the address of the value 15 and so it throws an error.