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
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( in the cell.
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 , (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
Press Shift + ← (Left Arrow key) to select the entered table array.
Switch to absolute references to fix the range
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 , (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
Enter , (comma). range lookup will be displayed, this time selecting FALSE as an example.
Copy the function to another cell
Press Ctrl + Enter to confirm. Press Ctrl + C to copy the function to another cell.
Select cell to paste
Press ↓ (Down Arrow key) to select the cell where you want to paste the copied function.
Fixed VLOOKUP range
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.