Meet fellow New Skills Academy students in our study group. Join now!

Microsoft Excel Advanced Certificate

100%
Module 58: VLOOKUP with MAX

Module 58: VLOOKUP with MAX

Duration: Approx 20 minutes

58.1 VLOOKUP with MAX


You can use VLOOKUP with MAX function to look up the highest value in any data set.

The MAX function will identify the highest value in a data set and the VLOOKUP function will return any specific data you ask it to within that row. When using VLOOKUP with MAX you will need to use two separate formulas to see both the MAX value and the data you are searching for.

For example, in a table with student test marks you would use the MAX function to return the highest mark, and a VLOOKUP function to see which student achieved it.

The first is a standard MAX function: =MAX(column)

To see which student achieved this mark you use the VLOOKUP function: =VLOOKUP(MAX(Column),Table,Col_num,0)

Column: The column in which the MAX data you need is stored.

Table: The full table of data.

The first column selected needs to be the column, which has the MAX data you need.

Col_num: The column in which the data you need returned is stored. If it is the sixth column in the table array, use the number 6. If you don't need the MAX data shown, the VLOOKUP formula can be used on its own.

58.2 Instructional Video



VLOOKUP with MAX – 2m 52s







58.3 Task


Estimated Time: 10 minutes
Download the worksheet that accompanies this video by clicking below. Once downloaded, follow the video above and you will see how to use VLOOKUP with MAX.

Download Worksheet

Download Worksheet - Without Formula


<< Previous Next >>

Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


1
Module 1: Add a Password
20 minutes
2
Module 2 : AND Function
20 minutes
3
Module 3 : Auto Updating Drop-down Lists
20 minutes
4
Module 4 : CEILING Formula
20 minutes
5
Module 5 : Charts that Update Automatically
20 minutes
6
Module 6 : CHOOSE Formula
20 minutes
7
Module 7 : Compare 2 Lists with VLOOKUP Function
20 minutes
8
Module 8 : CORREL Function
20 minutes
9
Module 9 : Create an Advanced Calculator
20 minutes
10
Module 10 : Create a Combo Chart
20 minutes
11
Module 11 : Create a Custom Tab
20 minutes
12
Module 12 : Create a Pivot Chart
20 minutes
13
Module 13 : Create Visual In-cell Indicators
20 minutes
14
Module 14 : Create Visual In-cell Indicators with Icon Sets
20 minutes
15
Module 15: Customise the Status Bar
20 minutes
16
Module 16: DATEDIF Function
20 minutes
17
Module 17: DATEVALUE Function
20 minutes
18
Module 18: DAVERAGE Function
20 minutes
19
Module 19 : DCOUNT Function
20 minutes
20
Module 20 : DCOUNTA Function
20 minutes
21
Module 21 : Depreciation Formula
20 minutes
22
Module 22 : DGET Function
20 minutes
23
Module 23 : DMAX Function
20 minutes
24
Module 24 : DPRODUCT
20 minutes
25
Module 25: DSUM Function
20 minutes
26
Module 26: EDATE Function
20 minutes
27
Module 27: EOMONTH Function
20 minutes
28
Module 28: Find and Replace
20 minutes
29
Module 29: FIND Function
20 minutes
30
Module 30: INDEX Function
20 minutes
31
Module 31: INDEX MATCH Function
50 minutes
32
Module 32: INDIRECT Function
20 minutes
33
Module 33: Insert Serial Number Automatically
20 minutes
34
Module 34: IPMT Function
20 minutes
35
Module 35: ISBLANK Function
20 minutes
36
Module 36: ISERR Function
20 minutes
37
Module 37: ISLOGICAL Function
20 minutes
38
Module 38: ISNONTEXT Function
20 minutes
39
Module 39: ISNUMBER Function
20 minutes
40
Module 40: ISODD Function
20 minutes
41
Module 41: Isolate First Name
20 minutes
42
Module 42: ISTEXT Formula
20 minutes
43
Module 43: LEFT Function
20 minutes
44
Module 44: LEN Function
20 minutes
45
Module 45: MATCH Function
20 minutes
46
Module 46: VLOOKUP Multiple Results at Once
20 minutes
47
Module 47: OR Function
20 minutes
48
Module 48: PMT Function
20 minutes
49
Module 49: REPLACE Function
20 minutes
50
Module 50: Select all Cells with Comments
20 minutes
51
Module 51: Sparkline Charts in Excel
20 minutes
52
Module 52: Straight Line Depreciation
20 minutes
53
Module 53: Strikethrough
20 minutes
54
Module 54: Sum of Years Depreciation
20 minutes
55
Module 55: Timeline in Pivot Table
20 minutes
56
Module 56: VLOOKUP with 2 Criteria
20 minutes
57
Module 57: VLOOKUP with DropDown List
20 minutes
58
Module 58: VLOOKUP with MAX
20 minutes
59
Module 59: VLOOKUP with MIN
20 minutes
60
Module 60: Watch Window In Excel
20 minutes

Module 13 : Create Visual In-cell Indicators

data formatting

Visit Module

Module 29: FIND Function

29.1 FIND Function


You can use the FIND function in Excel to get the location of specific text inside a string of text.

 also find multiple instances of a letter. FIND can be used in conjunction with other logical formulas such as

 IF, ELSE, OR, AND, VLOOKUP, HLOOKUP and INDIRECT to look up data.

=FIND(find_text, within_text, [start_num})

 

Visit Module

Module 37: ISLOGICAL Function

Check if value inc ell is logical. ISLOGICAL part of IS group returning TRUE/FALSE. TRUE if data is logical -FALSE if not. Used with cell address to provide a value. Example is B4 logical based. TO DO =ISLOGICAL(value)-- value, the value to check. 

THIS CAN be used with IF to return specific data ( if the value is TEXT) 

Visit Module

Module 50: Select all Cells with Comments

Comments are a useful feature, stores info in a cell. commetted cells have a lil red dot in the corner. Some worksheets have plenty of comments this finds them and allows you the edit them. TO DO - 

Visit Module

Module 1: Add a Password

pass word 

Visit Module

Module 26: EDATE Function

26.1 EDATE Function


The EDATE function in Excel allows you to return the serial number of a date that is the specified amount of months after a set date.

 

You can then convert this into a date format.

 

The formula is written as: =EDATE(start_date, months)

 

Start_date - This is the start date that you would like to calculate the months from.

 

Months - The number of months you would like to calculate from the start date.

 

It is worth noting that the first date that can be used with this formula is January 1, 1900, as Excel does not use serial numbers for anything prior to this period. Any date before January 1, 1900 will only return an error.
Visit Module

Module 53: Strikethrough

As with other Microsoft Office programs and similar software or apps for data management and word processing, it is possible to Strikethrough text in Excel.
 
You can choose single cells, multiple cells, or even entire rows or columns. This is useful to note for a variety of reasons, such as when you may wish to disregard a comment within a document but not delete it entirely.
 
To use strikethrough, you can add it to your Quick Access Toolbar for easy access:-
 
Click the arrow next to the Quick Access Toolbar, choose More Commands
 
Click the drop-down to choose All Commands
 
Then scroll down to Strikethrough, select it and click Add, then OK.
 
You can then select the text you wish to put a line through and choose the Strikethrough button in the Quick Access Toolbar.
Visit Module

Module 2 : AND Function

So the cell is greater than 80, and cell > 80, and cell > 80 will either make the cell say true or false. 

=AND(D6>80,AND(E6>80),AND(F6>80) 

 

OR WITH IF

 

Visit Module

Module 3 : Auto Updating Drop-down Lists

Make the date you want a list from a table so you can add new infomation. Go to Data then date validation 

Visit Module

Module 5 : Charts that Update Automatically

Charts that Update Automatically

To make a chart update automatically you can select your existing list and format it as a table by going to the Home tab and clicking on Format as Table. Now you can add to the bottom of your source list and the data will automatically appear in your chart.

GOOGLE SHEET

Use Dynamic Ranges: If you expect new data to be added regularly, consider using functions like OFFSET or INDIRECT to define a dynamic data range. This way, the range will automatically adjust as you add new data.

  • Example: =OFFSET(A1,0,0,COUNTA(A:A),COUNTA(1:1)) – This formula will create a dynamic range starting from cell A1, expanding downward and rightward automatically as new data is added.
Visit Module

Module 6 : CHOOSE Formula

the first argument is where the cell is going to be placed in and the following arguments are values starting from the same first cell . The following arguments are where you want the results to be . this is a look up function like v and h

Visit Module

Module 7 : Compare 2 Lists with VLOOKUP Function

Comparing two lists in Excel using the VLOOKUP formula is a common task that can help you find matches or differences between these lists. Here’s a step-by-step guide on how to do it:

Step 1: Prepare Your Lists

  1. Arrange Lists: Ensure your two lists are in separate columns. For example, List 1 is in Column A, and List 2 is in Column B.

Step 2: Use the VLOOKUP Formula

  1. Formula Placement: Click on the cell where you want the comparison results to appear. This could be next to your first list (e.g., in Column C, next to List 1).

  2. Enter VLOOKUP Formula: The basic syntax of the VLOOKUP formula is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

    • lookup_value: The value in List 1 you want to find in List 2. For example, if you're starting with the first item in List 1, this would be A2.
    • table_array: The range that contains List 2. If your List 2 is in B1 to B100, then the range is B$1:B$100. The dollar signs ($) fix the range so that it doesn’t shift when you copy the formula down.
    • col_index_num: Since you’re looking up in a single column (List 2) and you want to return the matching value from this column, this number is 1.
    • [range_lookup]: Use FALSE to find an exact match.

  3. Complete the Formula: For instance, if you are comparing the value in cell A2 with values in column B (from B1 to B100), the formula would be =VLOOKUP(A2, B$1:B$100, 1, FALSE).

Step 3: Drag the Formula Down

  1. Copy Formula: After entering the formula in the first cell (e.g., C2), drag the fill handle (a small square at the bottom right of the cell) down to copy the formula to the other cells.

Step 4: Analyze the Results

  1. Interpreting Results: The VLOOKUP formula will return the matching value from List 2. If there’s no match, it will return an error (typically #N/A).
  2. Highlight Differences: Use Conditional Formatting to highlight cells in your results column that contain errors, indicating items in List 1 that are not in List 2.

Tips:

  • Data Consistency: Ensure that the data in both lists are consistent (e.g., no extra spaces, same case) to avoid missed matches.
  • Error Handling: You can wrap your VLOOKUP formula in an IFERROR function to handle errors more gracefully. For example: =IFERROR(VLOOKUP(A2, B$1:B$100, 1, FALSE), "Not Found"). This will display "Not Found" instead of an error if there’s no match.
  • Duplicate Values: VLOOKUP will return the first match it finds. If List 2 contains duplicates, it won’t necessarily find all instances.

By using the VLOOKUP formula, you can effectively compare two lists in Excel to identify matches or differences, which is especially useful in various data analysis tasks.

Visit Module

Module 8 : CORREL Function

 

Correl Formula

 

 

 

 

 

 

 

 

 

 

Month

Avg Temp

Air Cond.

Sales

 

Advertising

 Costs

Sales

Jan

20

100

 

£2,000 

£20,000 

Feb

30

200

 

£1,000 

£30,000 

Mar

30

300

 

£5,000 

£20,000 

Apr

40

200

 

£1,000 

£40,000 

May

50

400

 

£8,000 

£40,000 

Jun

50

400

 

£1,000 

£20,000 

 

 

 

 

 

 

 

Correlation

86%

 

Correlation

28%

 

 

 

 

 

 

 

 

 

 

 

 

This function examines two sets of data to determine the degree of relationship between the two sets

 

 

 

 

 

The result will be a decimal between 0 and 1.

 

 

 

 

 

The larger the result, the greater the correlation.

 

 

 

 

 

 

 

 

 

 

 

In Table 1, the Monthly temperature is compared against the Sales of air conditioning units.

 

 

 

 

 

The correlation shows that there is an 0.864 relationship between the data.

 

 

 
Visit Module

Module 52: Straight Line Depreciation

Microsoft Office Course says 100% complete but I am on Section 52.

Visit Module

Frequently Asked Questions

To progress on to the next module of your course scroll to the bottom of the current module and press the “NEXT” button. This will take you directly to the following module.

Once you have completed the last module of the course please click on the button at the bottom of the module that says “START”. This will take you to the test.

The pass mark for the end of course test is 70%

Yes, you can re-take your test as many times as you need to.

No, once you have passed a test it is not possible to retake it.

When logging on to your course directly through our site you have lifetime access.

Click on the “My Profile” button at the top of any page on our site. This will take you to your profile page. Once there scroll down to the section headed “Edit Your Profile” and enter your new password.

Please click here to contact our student support team

If you would like to go back and view a completed module you can do this by clicking the “Course Modules” option from the side bar menu of your course. This will take you to a list of the modules contained within your course. Simply select the module you wish to study again.

Please click here to find out how to save your course modules as PDF files

Worksheets

Module 1: Add a Password

Module 1: Add a Password

Module 2 : AND Function

Module 2 : AND Function

Module 3 : Auto Updating Drop-down Lists

Module 3 : Auto Updating Drop-down Lists

Module 4 : CEILING Formula

Module 4 : CEILING Formula

Module 5 : Charts that Update Automatically

Module 5 : Charts that Update Automatically

Module 6 : CHOOSE Formula

Module 6 : CHOOSE Formula

Module 7 : Compare 2 Lists with VLOOKUP Function

Module 7 : Compare 2 Lists with VLOOKUP Function

Module 8 : CORREL Function

Module 9 : Create an Advanced Calculator

Module 10 : Create a Combo Chart

Module 10 : Create a Combo Chart

Module 11 : Create a Custom Tab

Module 12 : Create a Pivot Chart

Module 12 : Create a Pivot Chart

Module 13 : Create Visual In-cell Indicators

Module 13 : Create Visual In-cell Indicators

Module 14 : Create Visual In-cell Indicators with Icon Sets

Module 14 : Create Visual In-cell Indicators with Icon Sets

Module 15: Customise the Status Bar

Module 16: DATEDIF Function

Module 16: DATEDIF Function

Module 17: DATEVALUE Function

Module 17: DATEVALUE Function

Module 18: DAVERAGE Function

Module 18: DAVERAGE Function

Module 19 : DCOUNT Function

Module 19 : DCOUNT Function

Module 20 : DCOUNTA Function

Module 20 : DCOUNTA Function

Module 21 : Depreciation Formula

Module 21 : Depreciation Formula

Module 22 : DGET Function

Module 22 : DGET Function

Module 23 : DMAX Function

Module 23 : DMAX Function

Module 24 : DPRODUCT

Module 24 : DPRODUCT

Module 25: DSUM Function

Module 25: DSUM Function

Module 26: EDATE Function

Module 26: EDATE Function

Module 27: EOMONTH Function

Module 27: EOMONTH Function

Module 28: Find and Replace

Module 29: FIND Function

Module 29: FIND Function

Module 30: INDEX Function

Module 30: INDEX Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 32: INDIRECT Function

Module 32: INDIRECT Function

Module 33: Insert Serial Number Automatically

Module 33: Insert Serial Number Automatically

Module 34: IPMT Function

Module 34: IPMT Function

Module 35: ISBLANK Function

Module 35: ISBLANK Function

Module 36: ISERR Function

Module 36: ISERR Function

Module 37: ISLOGICAL Function

Module 37: ISLOGICAL Function

Module 38: ISNONTEXT Function

Module 38: ISNONTEXT Function

Module 39: ISNUMBER Function

Module 39: ISNUMBER Function

Module 40: ISODD Function

Module 40: ISODD Function

Module 41: Isolate First Name

Module 41: Isolate First Name

Module 42: ISTEXT Formula

Module 42: ISTEXT Formula

Module 43: LEFT Function

Module 43: LEFT Function

Module 44: LEN Function

Module 44: LEN Function

Module 45: MATCH Function

Module 45: MATCH Function

Module 46: VLOOKUP Multiple Results at Once

Module 46: VLOOKUP Multiple Results at Once

Module 47: OR Function

Module 47: OR Function

Module 48: PMT Function

Module 48: PMT Function

Module 49: REPLACE Function

Module 49: REPLACE Function

Module 50: Select all Cells with Comments

Module 51: Sparkline Charts in Excel

Module 51: Sparkline Charts in Excel

Module 52: Straight Line Depreciation

Module 52: Straight Line Depreciation

Module 53: Strikethrough

Module 53: Strikethrough

Module 54: Sum of Years Depreciation

Module 54: Sum of Years Depreciation

Module 55: Timeline in Pivot Table

Module 56: VLOOKUP with 2 Criteria

Module 56: VLOOKUP with 2 Criteria

Module 57: VLOOKUP with DropDown List

Module 57: VLOOKUP with DropDown List

Module 58: VLOOKUP with MAX

Module 58: VLOOKUP with MAX

Module 59: VLOOKUP with MIN

Module 59: VLOOKUP with MIN

Module 60: Watch Window In Excel

Module PDF's

Module 1: Add a Password

Module 2 : AND Function

Module 3 : Auto Updating Drop-down Lists

Module 4 : CEILING Formula

Module 5 : Charts that Update Automatically

Module 6 : CHOOSE Formula

Module 7 : Compare 2 Lists with VLOOKUP Function

Module 8 : CORREL Function

Module 9 : Create an Advanced Calculator

Module 10 : Create a Combo Chart

Module 11 : Create a Custom Tab

Module 12 : Create a Pivot Chart

Module 13 : Create Visual In-cell Indicators

Module 14 : Create Visual In-cell Indicators with Icon Sets

Module 15: Customise the Status Bar

Module 16: DATEDIF Function

Module 17: DATEVALUE Function

Module 18: DAVERAGE Function

Module 19 : DCOUNT Function

Module 20 : DCOUNTA Function

Module 21 : Depreciation Formula

Module 22 : DGET Function

Module 23 : DMAX Function

Module 24 : DPRODUCT

Module 25: DSUM Function

Module 26: EDATE Function

Module 27: EOMONTH Function

Module 28: Find and Replace

Module 29: FIND Function

Module 30: INDEX Function

Module 31: INDEX MATCH Function

Module 32: INDIRECT Function

Module 33: Insert Serial Number Automatically

Module 34: IPMT Function

Module 35: ISBLANK Function

Module 36: ISERR Function

Module 37: ISLOGICAL Function

Module 38: ISNONTEXT Function

Module 39: ISNUMBER Function

Module 40: ISODD Function

Module 41: Isolate First Name

Module 42: ISTEXT Formula

Module 43: LEFT Function

Module 44: LEN Function

Module 45: MATCH Function

Module 46: VLOOKUP Multiple Results at Once

Module 47: OR Function

Module 48: PMT Function

Module 49: REPLACE Function

Module 50: Select all Cells with Comments

Module 51: Sparkline Charts in Excel

Module 52: Straight Line Depreciation

Module 53: Strikethrough

Module 54: Sum of Years Depreciation

Module 55: Timeline in Pivot Table

Module 56: VLOOKUP with 2 Criteria

Module 57: VLOOKUP with DropDown List

Module 58: VLOOKUP with MAX

Module 59: VLOOKUP with MIN

Module 60: Watch Window In Excel

My Notes