Jun 15 2022 12:03 PM
Jun 15 2022 12:20 PM
Jun 15 2022 02:33 PM
$A2 The $ sign locks the lookup value in column A. If you enter the formula in cell C2 and drag it down or to the left or right the lookup value will be a cell in column A. The row number will change to e.g. $A3 or $A4 but the column A won't.
Test2!$A$2:$C$8 The table_array has the 3 columns A, B and C and they are locked by the $ signs. It won't change no matter if you drag the formula up or down or left or right. Vlookup tries to look up the lookup value in range Test2!$A$2:$A$8. An exact match is done because of the range lookup FALSE.
COLUMN(Test2!C1) This currently returns 3 because the number of column C is 3 in any sheet. If there is an exact match the corresponding entry of range Test2!$C$2:$C$8 is returned because Test2!$C$2:$C$8 is the 3rd column of the table array. C1 is not locked with the $ sign and therefore changed to B1 if you drag the formula from cell C2 to B2. In cell B2 the formula shows COLUMN(Test2!B1) with the result 2 because 2 is the number of column B. If there is an exact match the corresponding entry of range Test2!$B$2:$B$8 is returned because Test2!$B$2:$B$8 is the 2nd column of the table array ( Test2!$A$2:$C$8 ).
FALSE The VLOOKUP is looking for an exact match of the lookup value in range Test2!$A$2:$A$8 with this command.