Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel ROW Function
  • Extract all partial matches in Excel
  • How to use Excel OFFSET function
  • How to get last row in text data in Excel
  • How to get last row in mixed data with blanks in Excel

Data Analysis Examples

  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • How To Create Frequency Distribution in Excel
  • How To Filter Data in Excel
  • How To Create Pareto Chart in Excel
  • Move chart to a separate worksheet in Excel

Data Validation Examples

  • Data validation must not exist in list
  • Excel Data validation no punctuation
  • Excel Data validation allow uppercase only
  • Prevent invalid data entering in specific cells
  • Excel Data validation only dates between

Tag: VLOOKUP function

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Perform case-sensitive Lookup in Excel

by

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 …

Continue Reading

Left Lookup in Excel

by

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 …

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

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
  • How to check if cell contains number in Excel
  • DBCS function: Description, Usage, Syntax, Examples and Explanation
  • TRIM function: Description, Usage, Syntax, Examples and Explanation
  • Remove line breaks in a cell in Excel
  • How to extract domain from email address in Excel
  • Generate series of dates by weekends in Excel
  • Get age from birthday in Excel
  • Convert Excel time to decimal seconds
  • Add days exclude certain days of week in Excel
  • How to get Weekdays, Working days between Two Dates in Excel
  • IPMT function: Description, Usage, Syntax, Examples and Explanation
  • PRICE function: Description, Usage, Syntax, Examples and Explanation
  • PRICEDISC function: Description, Usage, Syntax, Examples and Explanation
  • TBILLEQ function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate compound interest in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles