Google sheets vlookup multiple search keys

When working with interrelated data, one of the most common challenges is finding information across multiple sheets. You often perform such tasks in everyday life, for example when scanning a flight schedule board for your flight number to get the departing time and status. But that's not true! The VLOOKUP function in Google Sheets is designed to perform a vertical lookup google sheets vlookup multiple search keys search for a key value unique identifier down the first column in a specified range and return a value in the same row from another column.

First of all, let me clarify one thing. There are two aspects to the usage of the Vlookup with multiple criteria in Google Sheets. Let me illustrate the same. Vlookup multiple criteria from a single column :. Vlookup multiple criteria from multiple columns : I am going to explain this topic in this article in detail.

Google sheets vlookup multiple search keys

When you have to refer to data from multiple sheets or tables, physically looking them up can be painstaking. This table could be on the same sheet or a different sheet. So if you have multiple criteria or want to check multiple interrelated columns at the same time, you have to find a way around this limitation. For example, in the following image, we have two tables. One that contains personal information about employees and another that contains their total sales on a particular month. Both tables have a common column, the Employee ID, so this could be considered as a key-value or a unique identifier in both tables. This is what you will enter in cell F The above case only involved looking up a single criterion, a match for the Employee ID. However, most of the time the criterion is more complex than that. Using IF functions will also be overkill because you will then need to deal with multiple nested IFs and nobody likes that! Let us take the following example, where you have one table containing the Bonus corresponding to each Department and Area code. In table 2, we need to look up the Bonus corresponding to a particular Department in a particular Area code and display the retrieved value in the Bonus column column E. In our example, we can insert the Helper column right before the Department column, so that it can be the first column of the search range. The Helper column can contain a combination of the Department and Area code for each row, separated by a space. This will ensure that the search range does not shift when the formula is copied to the rest of the cells.

The source data is already familiar to you: we will be pulling information from the Status column based on the Order ID :. Now, I am able to quickly answer several questions about the highest-grossing movie in any given year by simply changing the year named in cell I3. Sumit's unique approach involves breaking down intricate spreadsheet functions into easily digestible parts.

You may use it to answer a specific question using your dataset. You should use this function when your data is organized in columns, meaning each column lists a specific criteria, and data is related across each row. To begin, you'll need your tab open to your spreadsheet. This can make it a little easier to stay organized if you want to add new rows, sort, or filter your data. If you prefer, you can also create a separate sheet tab in your spreadsheet to house your functions. In our Practice Sheet, if you want to know what movie topped the box office in the year , your search key would be

Get started now. Last Modified: July 20, - 12 min read. Make your Google Sheets work for you. The tables can occupy the same sheet, a different tab, a separate sheet, or a spreadsheet file. You want to produce another table within the same Sheet that only includes the product ID and price. Enter the product ID value into the formula or click the cell F2 to automatically add it. Then add a comma. Step 3: Add the range second parameter and specify the set of columns you want to include in the search. Then add a comma after. To use Formula Builder, you need to install Coefficient.

Google sheets vlookup multiple search keys

For example, you may want to use first name and last name combined to search for a value using Vlookup. In this case, we want to combine search criteria to use in the Vlookup formula. The trick here is to nest a concatenation formula inside the Vlookup to combine the criteria prior to searching. We need to combine first name and last name before searching for the full name in the table. Yes, click here. This scenario is the opposite way round to the first one. In other words we have a complete search term, but our search table has multiple columns that need to be searched. For example, our search term is the full name of someone, but the search table has a column for first name and a column for last name. What we do is create a helper column in the search table, which combines the required columns to create a new search column.

Evil geniuses roster

When using VLOOKUP, your search key should be found in the first column of your range, and the related information you are seeking should be found in a column to the right of that column. To make the below Vlookup examples easier to follow, you can open the sample Vlookup Google sheet. For example, in my Practice Sheet, I supplemented the movie title VLOOKUP function with additional functions to pull the primary director and box office earnings, all tied to the original search key in I3. So when 'lasagne' is chosen on the drop down on Sheet 2, it pulls the total price for Lasagne through from Sheet 1 and places it on Sheet 2, then changes depending on the drop down chosen.. Chris Daniel January 25, Thank you for any assistance you can give me. I am sorry about the typo. Try this. The above example is with two criteria. I'll look into it and try to help. Specializes in spreadsheet training for businesses , catering to small business owners and data-focused teams in larger businesses.

This post explains how to use Vlookup to find a search key in multiple columns in a matrix in Google Sheets.

Vlookup multiple criteria from a single column : If you are looking for Vlookup formula with more than one criterion from the first column, find the details here — How to Use Vlookup to Return An Array Result in Google Sheets. I missed the sort order to put at the end. Expiry Item Description Lot No. For example, return all Safety Helmet details in each row. Multiple Vlookup matches creates a new dataset in the location of your choice according to your requirements. But I do hope you get what I am trying to solve here :D Thank you! Sorry, it's my 1st time to post here. See this supporting tutorial. Thank you for the tutorial. First of all, let me clarify one thing. I will post the link here then.