Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Sort One Column or Multiple Columns in Excel
  • Error Bars in Excel
  • How to combine 2 or more chart types in a single chart in Excel
  • Move chart to a separate worksheet in Excel
  • How to do a t-Test in Excel?

References

  • How to use Excel OFFSET function
  • Last row number in range
  • Multi-criteria lookup and transpose in Excel
  • Two-column Lookup in Excel
  • How to get relative column numbers in a range in Excel

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation with conditional list
  • Excel Data validation must begin with
  • Excel Data validation allow weekday only
  • Excel Data validation require unique number

Tag: VLOOKUP function

Vlookup Examples in Excel

by

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. …

Continue Reading

Excel VLOOKUP and HLOOKUP functions Examples

by

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 …

Continue Reading

VLOOKUP function: Description, Usage, Syntax, Examples and Explanation

by

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 …

Continue Reading

How to calculate two-way lookup VLOOKUP in Excel Table

by

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. …

Continue Reading

Create hyperlink with VLOOKUP in Excel

by

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 …

Continue Reading

How to translate letters to numbers in Excel

by

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 …

Continue Reading

Win loss points calculation in Excel

by

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 …

Continue Reading

Group numbers with VLOOKUP in Excel

by

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 …

Continue Reading

Group arbitrary text values in Excel

by

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 …

Continue Reading

Map inputs to arbitrary values in Excel

by

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 …

Continue Reading

Map text to numbers in Excel

by

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: …

Continue Reading

Group times into unequal buckets in Excel

by

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 …

Continue Reading

Self-contained VLOOKUP in Excel

by

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 …

Continue Reading

Two-way lookup with VLOOKUP in Excel

by

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 …

Continue Reading

Basic Tax Rate calculation with VLOOKUP in Excel

by

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 …

Continue Reading

VLOOKUP by date in Excel

by

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. …

Continue Reading

Calculate grades with VLOOKUP in Excel

by

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 …

Continue Reading

VLOOKUP from another workbook in Excel

by

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 …

Continue Reading

Dynamic lookup table with INDIRECT in Excel

by

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 …

Continue Reading

VLOOKUP from another sheet in Excel

by

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 …

Continue Reading

Extract data with helper column in Excel

by

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 …

Continue Reading

VLOOKUP with 2 lookup tables in Excel

by

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 …

Continue Reading

Faster VLOOKUP with 2 VLOOKUPS in Excel

by

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 …

Continue Reading

VLOOKUP with multiple criteria in Excel

by

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 …

Continue Reading

Exact match lookup with INDEX and MATCH in Excel

by

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 …

Continue Reading

VLOOKUP with numbers and text in Excel

by

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 …

Continue Reading

Get employee information with VLOOKUP in Excel

by

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. …

Continue Reading

VLOOKUP with two client rates in Excel

by

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 …

Continue Reading

Get first text value in a list in Excel

by

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 …

Continue Reading

VLOOKUP without #N/A error in Excel

by

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 …

Continue Reading

Get nth match with VLOOKUP in Excel

by

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 …

Continue Reading

Lookup up cost for product or service in Excel

by

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 …

Continue Reading

Lookup with variable sheet name in Excel

by

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” …

Continue Reading

Merge tables with VLOOKUP in Excel

by

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 …

Continue Reading

Multiple chained VLOOKUPs in Excel

by

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 …

Continue Reading

Partial match with VLOOKUP in Excel

by

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 …

Continue Reading

How to use Excel VLOOKUP Function

by

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 …

Continue Reading

Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions

by

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 …

Continue Reading

How to Calculate Tax Rates in Excel

by

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 …

Continue Reading

Excel Advanced Lookup using Index and Match Functions

by

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 …

Continue Reading

Posts navigation

  • 1
  • 2
  • Next

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Return blank if in Excel
  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • How to return blank in place of #DIV/0! error in Excel
  • How to use Excel FALSE Function

Date Time

  • DAY function: Description, Usage, Syntax, Examples and Explanation
  • Get last working day in month in Excel
  • YEARFRAC function: Description, Usage, Syntax, Examples and Explanation
  • Dynamic date list in Excel
  • How to get workdays between dates in Excel

Grouping

  • Running count group by n size in Excel
  • Categorize text with keywords in Excel
  • Group times into 3 hour buckets in Excel
  • Map inputs to arbitrary values in Excel
  • Group times into unequal buckets in Excel

General

  • How to add sequential row numbers to a set of data in Excel
  • 3D SUMIF for multiple worksheets in Excel
  • Spell Check in Excel
  • Sum by group in Excel
  • Basic text sort formula in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning