Using VLOOKUP with multiple ranges [Google Sheets]

Sample application of VLOOKUP with arrays

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:

Batch 1 Employees
Batch 2 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: