Excel Hack

VLOOKUP range is fixed with shortcut (F4)

Excel’s VLOOKUP function is a useful function that allows you to match data from list. However, when using the VLOOKUP function, the reference range may be misaligned. This is a common problem. By using the shortcut keys introduced here, you can fix the reference range in the VLOOKUP function. Absolute references with fixed ranges can be easily performed, so please refer to this section.

Shortcut to fix VLOOKUP range

Windows
F4

Mac
F4 or command + T

How to fix the VLOOKUP range

This is a shortcut to fix a VLOOKUP range with an absolute reference.

Total Time: 1 minute

Enter VLOOKUP

Enter VLOOKUP

Enter =VLOOKUP( in the cell.

Enter lookup value

Enter lookup value

Enter lookup value. This value refers to the cell where you enter the code needed to tie the listing together. In this example, enter A8.

Enter table array

Enter table array

Enter , (comma), then table array. A table array is a range from which data is retrieved. In this example, enter A2:B5.

Select the entered table array

Select the entered table array

Press Shift + (Left Arrow key) to select the entered table array.

Switch to absolute references to fix the range

Switch to absolute reference

Press F4 to switch table arrays to absolute references. Press F4 once to make both rows and columns absolute references. This will prevent the reference range from shifting.

Enter column index number

Enter column index number

Enter , (comma), then the column index number. In this case, we want to extract the data in the second column from the left of the table, so we enter 2.

Select range lookup

Select range lookup

Enter , (comma). range lookup will be displayed, this time selecting FALSE as an example.

Copy the function to another cell

Press Ctrl + C

Press Ctrl + Enter to confirm. Press Ctrl + C to copy the function to another cell.

Select cell to paste

Select cells to paste

Press (Down Arrow key) to select the cell where you want to paste the copied function.

Fixed VLOOKUP range

VLOOKUP function copied

Press Enter to paste the copied function. Having fixed the range by absolute reference, the referenced range does not change even if the same formula is copied to another cell.