The VLOOKUP function is one of the most popular functions in Excel. This chapter contains many easy to follow VLOOKUP examples for Exact Match, Approximate Match, Right Lookup, First Match, Case-insensitive, Multiple Criteria, #N/A error and Multiple Lookup Tables. Exact Match Most of the time you are looking for an exact match when you use the VLOOKUP function in Excel. …
Learn all about Excel’s lookup & reference functions such as the VLOOKUP, HLOOKUP, CHOOSE, MATCH and INDEX function. Navigation: Formula Tab → Function Library Group → Lookup and Reference Vlookup VLOOKUP stands for Vertical lookup. The VLOOKUP function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column …
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 …
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. …
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 …
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 …
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 …
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 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 …
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 …
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: …
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 …
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 …
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 …
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 …
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. …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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. …
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 …
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 …
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 …
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 …
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 …
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” …
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 …
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 …
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 …
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 …
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 …
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 …
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 …