What is VLOOKUP function in Excel? VLOOKUP function is one of the lookup and reference functions in Microsoft office Excel that is used when you need to find things in a table or a range by row. For instance, look up a price of an automotive part by the part number. In its simplest form, the VLOOKUP function says: =VLOOKUP(Value you want to…
Tag: VLOOKUP function
Create hyperlink with VLOOKUP in Excel
To create a hyperlink from a lookup, you can use the VLOOKUP function together with the HYPERLINK function. The hyperlink function allows you to create a working link with a formula. It takes two arguments: link_location and, optionally, friendly_name. Formula =HYPERLINK(VLOOKUP(name,table,column,0),name) Explanation In the example shown, the formula in F5 is: =HYPERLINK(VLOOKUP(E5,link_table,2,0),E5) How this formula works Working from the inside out, VLOOKUP looks…
How to calculate two-way lookup VLOOKUP in Excel Table
To do a two-way lookup in an Excel Table, you can use the MATCH function with a structured reference and VLOOKUP. See example below: Recall that VLOOKUP depends on the lookup value being to the left of the value being retrieved in a table. Generally, this means the lookup value will be the first value in the table. If you have data where…
How to translate letters to numbers in Excel
To translate letters in a string to numbers, you can use an array formula based on the TEXTJOIN and VLOOKUP functions, with a defined translation table to provide the necessary lookups. Formula {=TEXTJOIN(“”,1,VLOOKUP(T(IF(1,MID(A1,ROW (INDIRECT(“1:”&LEN(A1))),1))),xtable,2,0))} Explanation In the example shown, the formula in C5 is: {=TEXTJOIN(“”,1,VLOOKUP(T(IF(1,MID(B5,ROW (INDIRECT(“1:”&LEN(B5))),1))),xtable,2,0))} where “xtable” is the named range E5:F10. Note: this is an array formula and must be entered with…
Group times into unequal buckets in Excel
This tutorial shows how to Group times into unequal buckets in Excel using the example below; Formula =VLOOKUP(time,bucket_table,column,TRUE) Explanation This formula is a great example of how you can use VLOOKUP to group data in completely custom ways. If you need to group times into buckets, but the buckets are not equal (i.e. 12 AM-7 AM, 7 AM-12 PM, etc.)…
Map text to numbers in Excel
This tutorial shows how to Map text to numbers in Excel using the example below; Formula =VLOOKUP(text,lookup_table,2,0) Explanation To map or translate text inputs to arbitrary numeric values, you can use the VLOOKUP function with a simple table. In the example, we need to map five text values (statuses) to numeric status codes as follows: Status Code Started 10 Normal…
Map inputs to arbitrary values in Excel
This tutorial shows how to Map inputs to arbitrary values in Excel using the example below; Formula =VLOOKUP(input,map_table,column,0) If you need to map or translate inputs to arbitrary values, you can use the VLOOKUP function. In the example, we need to map the numbers 1-6 as follows: Input Output 1 10 2 81 3 17 4 23 5 13 6…
Group numbers with VLOOKUP in Excel
This tutorial shows how to Group numbers with VLOOKUP in Excel using the example below; Formula =VLOOKUP(value,group_table,column,TRUE) Explanation If you need to group by number, you can use the VLOOKUP function with a custom grouping table. This allows you to make completely custom or arbitrary groups. In the example shown, the formula in F7 is: =VLOOKUP(D5,age_table,2,TRUE) How this formula works This formula…
Group arbitrary text values in Excel
This tutorial shows how to Group arbitrary text values in Excel using the example below; Formula =VLOOKUP(value,table,column,0) Explanation IF you want to group or classify data based on arbitrary text values, you can use VLOOKUP instead of a long series of nested IF statements. The trick is to build a custom table that will map values to all the groups…
Win loss points calculation in Excel
This tutorial shows how to work Win loss points calculation in Excel using the example below; To assign points based on win/loss/tie results for a team, you can use a simple VLOOKUP formula, or a nested IF formula, as explained below. Formula =VLOOKUP(result,points_table,2,0) Explanation In the example shown, the formula in D5 is: =VLOOKUP(C5,points_table,2,0) How this formula works This is a…
VLOOKUP with numbers and text in Excel
This tutorial shows how to calculate VLOOKUP with numbers and text in Excel using the example below; Formula =VLOOKUP(val&””,table,col,0) Explanation A common problem with VLOOKUP is a mismatch between numbers and text. Either the first column in the table contains lookup values that are numbers stored as text, or the table contains numbers, but the lookup value itself is a…
VLOOKUP without #N/A error in Excel
This tutorial shows how to calculate VLOOKUP without #N/A error in Excel using the example below; Formula =IFERROR(VLOOKUP(value,table,2,FALSE),””) Explanation To hide the #N/A error that VLOOKUP throws when it can’t find a value, you can use the IFERROR function to catch the error and return any value you like. How the formula works When VLOOKUP can’t find a value in…
VLOOKUP with multiple criteria in Excel
This tutorial shows how to calculate VLOOKUP with multiple criteria in Excel using the example below; Formula =VLOOKUP(val1&val2,data,column,0) Explanation The VLOOKUP function does not handle multiple criteria natively. However, if you have control over source data, you can use a helper column to join multiple fields together, and use these fields like multiple criteria inside VLOOKUP. In the example shown, Column B…
VLOOKUP with two client rates in Excel
This tutorial shows how to calculate VLOOKUP with two client rates in Excel using the example below; Formula =VLOOKUP(client,rates,col,0)*hrs+VLOOKUP(client,rates,col,0)*hrs Explanation To lookup two different rates for the same client, and calculate a final charge, you can use a formula based on two VLOOKUP functions. In the example shown, the formula in E5 is: =VLOOKUP(B5,rates,2,0)*C5+VLOOKUP(B5,rates,3,0)*D5 where “rates” is the named range…
VLOOKUP with 2 lookup tables in Excel
This tutorial shows how to calculate VLOOKUP with 2 lookup tables in Excel using the example below; Formula =VLOOKUP(value,IF(test,table1,table2),col,match) Explanation To use VLOOKUP with a variable table array, you can use the IF function inside VLOOKUP to control which table is used. In the example shown the formula in cell E4 is: =VLOOKUP(D5,IF(C4<2,table1,table2),2,TRUE) This formula uses the number of years…
VLOOKUP from another sheet in Excel
This tutorial shows how to calculate VLOOKUP from another sheet in Excel using the example below; Formula =VLOOKUP(lookup,sheet!range,column,match) Explanation Using VLOOKUP from another sheet is very similar to using VLOOKUP on the same sheet. In the example shown, the formula in F5 is: =VLOOKUP(B5,Sheet2!$B$5:$C$104,2,0) Here, VLOOKUP pulls the correct building for each employee on Sheet2, into the table on Sheet1. How…
VLOOKUP from another workbook in Excel
This tutorial shows how to calculate VLOOKUP from another workbook in Excel using the example below; Formula =VLOOKUP(B5,[workbook]sheet!range,4,0) Explanation To lookup product data, pricing, or other information stored in a separate (external) workbook, you can use the VLOOKUP function with a full reference to the other workbook. In the example shown, the formula in C5 is: =VLOOKUP(B5,'[product data.xlsx]Sheet1′!$B$5:$E$13,4,0) Sample data…
VLOOKUP by date in Excel
This tutorial shows how to calculate VLOOKUP by date in Excel using the example below; Formula =VLOOKUP(date,table,col,FALSE) Explanation To retrieve a value on a specific date from a table, you can use the VLOOKUP function. In the example shown, the formula in F6 is: =VLOOKUP(E6,B6:C11,2,0) How this formula works This is a standard VLOOKUP formula. It requires a table with…
Two-way lookup with VLOOKUP in Excel
This tutorial shows how to calculate Two-way lookup with VLOOKUP in Excel using the example below; Formula =VLOOKUP(lookup_value,table,MATCH(col_name,col_headers,0),0) Explanation Note: Inside the VLOOKUP function, the column index argument is normally hard-coded as a static number. However, you can also create a dynamic column index by using the MATCH function to locate the right column. This technique allows you to create a dynamic two-way lookup,…
Self-contained VLOOKUP in Excel
This tutorial shows how to work Self-contained VLOOKUP in Excel using the example below; Formula =VLOOKUP(lookup,{table_array},column,match) Explanation To make a self-contained VLOOKUP formula, you can convert the table array to an array constant inside of VLOOKUP. In the example shown the formula in F7 is: =VLOOKUP(E7,{0,”F”;60,”D”;70,”C”;80,”B”;90,”A”},2,TRUE) How this formula works Normally, the second argument for VLOOKUP is the table_array, which is…
Partial match with VLOOKUP in Excel
This tutorial shows how to calculate Partial match with VLOOKUP in Excel using the example below; Formula =VLOOKUP(value&”*”,data,column,FALSE) Explanation If you want to retrieve information from a table based on a partial match, you can do so using VLOOKUP in exact match mode, and wildcards. In the example shown, the VLOOKUP formula looks like this: =VLOOKUP(val&”*”,data,2,FALSE) In this formula, val is a named range that…
Multiple chained VLOOKUPs in Excel
This tutorial shows how to work Multiple chained VLOOKUPs in Excel using the example below; Formula =IFERROR(VLOOKUP 1,IFERROR(VLOOKUP 2,VLOOKUP 3)) Explanation If you need to perform multiple lookups sequentially, based on whether the earlier lookups succeed or not, you can chain one or more VLOOKUPs together with IFERROR. In the example shown, the formula in L5 is: =IFERROR(VLOOKUP(K5,B5:C7,2,0),IFERROR(VLOOKUP(K5,E5:F7,2,0),VLOOKUP(K5,H5:I7,2,0))) How this formula…
Merge tables with VLOOKUP in Excel
This tutorial shows how to Merge tables with VLOOKUP in Excel using the example below; Formula =VLOOKUP($A1,table,COLUMN()-x,0) Explanation To merge tables, you can use the VLOOKUP function to lookup and retrieve data from one table to the other. To use VLOOKUP this way, both tables must share a common id or key. This article explains how join tables using VLOOKUP and a calculated column…
Lookup with variable sheet name in Excel
This tutorial shows how to Lookup with variable sheet name in Excel using the example below; Formula =VLOOKUP(val,INDIRECT(“‘”&sheet&”‘!”&”range”),col,0) Explanation To create a lookup with a variable sheet name, you can use the VLOOKUP function together with the INDIRECT function. In the example shown, the formula in C5 is: =VLOOKUP($B5,INDIRECT(“‘”&C$4&”‘!”&”B5:C11″),2,0) How this formula works The “month” tabs of the worksheet contain…
Lookup up cost for product or service in Excel
This tutorial shows how to Lookup up cost for product or service in Excel using the example below; Formula =VLOOKUP(product,table,column,FALSE) Explanation If you have a list of products or services (or related options), with associated costs, you can use VLOOKUP to find and retrieve the cost for a specific option. In the example shown, the formula in cell F6 is:…
Get nth match with VLOOKUP in Excel
This tutorial shows how to Get nth match with VLOOKUP in Excel using the example below; Formula =VLOOKUP(id_formula,table,4,0) Explanation To get the nth MATCH with VLOOKUP, you’ll need to add a helper column to your table that constructs a unique id that includes the count. If this isn’t practical, you can use an array formula based on INDEX and MATCH instead. =VLOOKUP(id&”-“&I6,data,4,0)…
Get first text value in a list in Excel
This tutorial shows how to Get first text value in a list in Excel using the example below; Formula =VLOOKUP(“*”,range,1,FALSE) Explanation If you need to get the first text value in a list (a one-column range) you can use the VLOOKUP function set to exact match, with a wildcard character for the lookup value. In the example the formula in…
Get employee information with VLOOKUP in Excel
This tutorial shows how to Get employee information with VLOOKUP in Excel using the example below; Formula =VLOOKUP(id,data,column,FALSE) Explanation If you want to retrieve employee information from a table, and the table contains a unique id to the left of the information you want to retrieve, you can easily do so with the VLOOKUP function. In the example shown, the…
Exact match lookup with INDEX and MATCH in Excel
This tutorial shows how to Exact match lookup with INDEX and MATCH in Excel using the example below; Formula {=INDEX(data,MATCH(TRUE,EXACT(val,lookup_col),0),col_num)} Explanation Case-sensitive lookup By default, standard lookups with VLOOKUP or INDEX + MATCH aren’t case-sensitive. Both VLOOKUP and MATCH will simply return the first match, ignoring case. However, if you need to do a case-sensitive lookup, you can do so…
Faster VLOOKUP with 2 VLOOKUPS in Excel
This tutorial shows how to calculate Faster VLOOKUP with 2 VLOOKUPS in Excel using the example below; Formula =IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA()) Explanation With large sets of data, exact match VLOOKUP can be painfully slow, but you can make VLOOKUP lightening fast by using two VLOOKUPS, as explained below. Notes: If you have a smaller set of data, this approach is…
Extract data with helper column in Excel
This tutorial shows how to Extract data with helper column in Excel using the example below; Formula =IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),””) Explanation One way to extract data in Excel is to use INDEX and MATCH with a helper column that marks matching data. This avoids the complexity of a more advanced array formula. In the example shown, the formula in H6 is: =IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),””)…
Basic Tax Rate calculation with VLOOKUP in Excel
This tutorial shows how to calculate Basic Tax Rate calculation with VLOOKUP in Excel using the example below; Formula =VLOOKUP(amount,tax_table,2,TRUE) Explanation To calculate a tax rate based on a simple tax rate table, you can use the VLOOKUP function. In the example shown, the formula in G5 is: =VLOOKUP(G4,tax_table,2,TRUE) How this formula works Note: This formula depends on a simple…
Calculate grades with VLOOKUP in Excel
This tutorial shows how to Calculate grades with VLOOKUP in Excel using the example below; Formula =VLOOKUP(score,key,2,TRUE) Explanation If you want to calculate grades using the VLOOKUP function, it’s easy to do. You just need to set up a small table that acts as the “key”, with scores on the left, and grades on the right. This table must be…
Dynamic lookup table with INDIRECT in Excel
This tutorial shows how to calculate Dynamic lookup table with INDIRECT in Excel using the example below; To allow a dynamic lookup table, you can use the INDIRECT function with named ranges inside of VLOOKUP. Formula =VLOOKUP(A1,INDIRECT(“text”),column) Explanation In the example shown the formula in G5 is: =VLOOKUP(F5,INDIRECT(E5),2,0) Background The purpose of this formula is to allow an easy way…
How to use Excel VLOOKUP Function
VLOOKUP function is actually quite easy to use once you understand how it works! This Excel tutorial explains how to use the VLOOKUP function with syntax and examples. Excel VLOOKUP Function Description VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. The VLOOKUP function performs a vertical lookup by searching for a value in the first…
How to Calculate Tax Rates in Excel
This example teaches you how to calculate the tax on an income using the VLOOKUP function in Excel. The following tax rates apply to individuals who are residents of Australia. Taxable income Tax on this income 0 – $18,200 Nil $18,201 – $37,000 19c for each $1 over $18,200 $37,001 – $87,000 $3,572 plus 32.5c for each $1 over $37,000 $87,001 – $180,000 $19,822 plus…
Excel Advanced Lookup using Index and Match Functions
Instead of using VLOOKUP, Use INDEX and MATCH and become an Excel pro. . To perform advanced lookups, you’ll need INDEX and MATCH. Match The MATCH function returns the position of a value in a given range. For example, the MATCH function below looks up the value 53 in the range B3:B9. Explanation: 53 (first argument) found at position 5 in the range B3:B9 (second…
Perform case-sensitive Lookup in Excel
By default, the VLOOKUP function performs a case-insensitive lookup. However, you can use the INDEX, MATCH and the EXACT function in Excel to perform a case-sensitive lookup. 1. For example, the simple VLOOKUP function below returns the salary of Mia Clark. However, we want to lookup the salary of MIA Reed (see cell G2). 2. The EXACT function in Excel returns TRUE if two strings…
Left Lookup in Excel
The VLOOKUP function only looks to the right. To look up a value in any column and return the corresponding value to the left, simply use the INDEX and the MATCH function. 1. The MATCH function returns the position of a value in a given range. Explanation: 104 found at position 4 in the range $G$4:$G$7. 2. Use this result and the INDEX function to return the 4th value in…
Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
To find the closest match to a target value in a data column, use the INDEX, MATCH, ABS and the MIN function in Excel. Use the VLOOKUP function in Excel to find an approximate match. 1. The ABS function in Excel returns the absolute value of a number. Explanation: C3-F2 equals -39. The ABS function removes the minus sign (-) from a negative number, making it positive.…