VLOOKUP is an incredibly simple but powerful function, allowing you to quickly index ‘search keys’ in your spreadsheet.
This short post will walk through using VLOOKUP in combination with arrays to reference multiple search ranges.
The Formula
=VLOOKUP('Search Key', ({Range 1; Range 2; ...}), 'index column', 'sort')
Example
In our ‘Employee ID Timesheet’ below, we have a list of time logs and IDs.
Say we want to have a name next to each of these, we can use a VLOOKUP function to search the names related to those IDs.
But instead of one employee database, our spreadsheet has two separate sheets for two batches of employees:
Here we can use our formula to use VLOOKUP and search both of the ranges.
And that’s it! Just copy the formula down your sheet and you’re all set.
That’s one more thing in your VLOOKUP utility belt 😉
Notes
- If you noticed, we used curly brackets within normal brackets in our formula. Using {} without the normal brackets may sometimes lead to an error where the VLOOKUP doesn’t recognise that we are using an array as our search range.
- When using arrays, make sure that your ranges have the same number of columns. Otherwise, you may get a ‘missing values’ error.
Aiman Fikri 