This article is a segment of Learn Google Sheets/ Excel – Basic to Advance, explains how does VLOOKUP Multiple Criteria work in both Google Sheets, covering various possible usage scenarios.
- VLOOKUP Formula Syntax
- How to Remember VLOOKUP Formula
- When It Works and When It Fails
- Usage Scenarios
VLOOKUP Formula Syntax
VLOOKUP full form is vertical lookup, it is used to lookup values from left to right in vertical fashion . Important thing to note is that, the value which you are trying to bring should be on the right side of the the lookup value
=VLOOKUP(lookup value, table_array, col_index_num, [range_lookup])
How to Remember VLOOKUP Formula
=VLOOKUP(
- lookup value = What you’re looking for
- table_array, = In which range data is present
- col_index_num = Column number from where you want value
- [range_lookup] = How accurate you want the lookup )
VLOOKUP Multiple Criteria Example
When It Works and When It Fails
VLOOKUP formula only works if the col_index_num is on the right side of the Lookup value column as in the above example “Unit Sold” is on the right side of the “Customer Name”
And remember the count of columns always start with Lookup value column as shown in above screenshot
Usage Scenarios
Note : $ symbol is added in the formula to fix the range and prevent shifting of range while dragging the formula
How to use VLOOKUP in Same Tab
=VLOOKUP(Search for Carol White, In the range from C2 to O12, Get 5th column, With Exact Match)
Explanation :
- A17 : As VLOOKUP is in same sheet hence we can directly select the lookup value cell which is A17
- $C$2:$O$12 : We started the array from Column C because the lookup value (Carol White) column is at Column C
- 5 : Unit Sold column is at 5th position from Column C (Customer Name)
- 0 : We want exact match for “Carol White” hence we entered 0
How to use VLOOKUP in Same Sheet but Different Tab
=VLOOKUP(Search for Carol White, In the range from C2 to O12 in tab “Data”, Get 5th column, With Exact Match)
Explanation :
- A17 : As VLOOKUP is in same sheet hence we can directly select the lookup value cell which is A17
- Data!$C$2:$O$12 : Data! defines the tab where your entire data is present and then the range
- 5 : Unit Sold column is at 5th position from Column C (Customer Name)
- 0 : We want exact match for “Carol White” hence we entered 0
How to use VLOOKUP between Different Google Sheet
=VLOOKUP(Search for Carol White, Import data from Sheet 1 : Range C2 to O12, Get 5th column, With Exact Match)
Click on Allow Access to populate the value
Explanation :
- A4 : Choose the cell containing the value you want to look up
- IMPORTRANGE(SheetURL,Tab!Range) : This formula will fetch data from Sheet 1 and helps VLOOKUP formula to populate the value
- 5 : Unit Sold column is at 5th position from Column C (Customer Name)
- 0 : We want exact match for “Carol White” hence we entered 0
How to use Index() and Match() Formula Instead of Vlookup
Easiest way out from this situation is to shift the col1_index_num to the right side of Lookup value column and if it is not possible then you can follow below approach
Lookup using Index() and Match() formulas
Explanation :
- INDEX(Data!$A$2:$O$12 : Index formula takes a table array and then we have to supply row and column number to get
- MATCH(A6,Data!$D$2:$D$12,0) : This formula will fetch data from Sheet 1 and helps VLOOKUP formula to populate the value
- MATCH($B$5,Data!$A$2:$O$2,0) : Unit Sold column is at 5th position from Column C (Customer Name)