Master Excel Lookups and References

Master the VLOOKUP function by using a lookup value in the leftmost table column to return a result from a specified column with the column index number.

Use VLOOKUP with approximate match to look up values in the leftmost column of a sorted table and return corresponding grade or discount via a column index, using absolute references.

Demonstrates vlookup with exact match to retrieve an employee’s details by ID. Learn to use the leftmost lookup column, a table array, absolute references, and exact vs approximate results.

Demonstrate VLOOKUP with exact match by locating an item number, selecting false for exact matching, and adjusting the column index from 2 to 3 to retrieve the unit price.

Concatenate the last name and first name with a comma and space to form the lookup value, then perform a vlookup with exact match to retrieve the email address.

Master Excel named ranges by creating and using names from selections, with names in formulas and absolute references; manage scope with workbook or sheet, and use name manager for organization.

Master vlookup with named ranges to fetch student names, course titles, and grades from sheets; name ranges, use exact matches for IDs, and apply approximate matches for grades (true).

Explore Excel tables, created by using format as table or insert table, and rename tables for precise named ranges, with headers, enabling table arrays for lookups and references.

Learn to use vlookup with an Excel table by converting ranges into named tables, then perform exact-match lookups across student and course tables to retrieve test scores.

Practice a practical vlookup exercise to enrich the orders sheet with employee and customer details. Use employee and customer ids to look up and add missing information from their sheets.

Navigate the VLOOKUP exercise solution to insert seven columns and populate employee and customer details using exact matches and absolute references. Create a two-window view to compare data.

Learn excel's hlookup, a horizontal lookup flipped from vlookup, specifying a table array, absolute references, a row index, and exact-match options to return values from the topmost row.

Apply hlookup to calculate tenure with the company, determine location and bonus percentage, and compute total pay and vacation days using the provided lookup table.

practice using date diff and today to calculate tenure in years, then apply hlookup with an approximate match to determine bonuses and vacation days from a lookup table.

Explore the match function in Excel, using exact and approximate match types to return the value's relative position in a row or column, with sorting requirements for 1 and -1.

Learn to combine vlookup with match to perform dynamic two-dimensional lookups, using exact match to pull values from different columns by calculating the column index.

Combine VLOOKUP with MATCH to compute extended price from quantity ordered, using absolute references and an exact match with a dynamic column index.

Master HLOOKUP and MATCH to determine status from donation level and volunteer hours, using absolute references and approximate matching with a descending hours table.

Master the index function for a two-dimensional lookup by returning the value at the intersection of a row and column within a data range, using absolute references when copying.

Master Excel lookups and references shows how to use the index function to display values from a one-dimensional range in either a single column or a single row.

Master Excel lookups with the INDEX function in reference form, returning values from multiple noncontiguous ranges by specifying ranges, area numbers, and row and column indexes.

Master Excel lookups by combining index and match for one-dimensional lookups, perform exact matches, and retrieve names, salaries, and birth dates across columns.

Master two-dimensional lookups in Excel by combining index and match to retrieve sales amounts by region and quarter, offering faster, flexible alternatives to vlookup.

Practice a two-dimensional lookup with index and match to retrieve an extended price based on product and quantity, using absolute references and exact and less-than matching.

Practice using index and match to fill missing data in the orders sheet from the employees and customers tables, creating self-contained formulas and removing temporary columns.

Demonstrate an index and match solution for a customer and employee lookup, using exact matches, parallel ranges, and copying formulas to retrieve first name, last name, and city.

Enhance index and match skills with the second assignment by building a formula to pull a base salary from lookup tables using department, title, and tenure.

Master Excel lookups with index and match by building a noncontiguous, multi-range base salary calculator using department, title, and years, with absolute references and named ranges.

Learn how to use array formulas in Excel with index and match to compute total payroll, using ctrl+shift+enter to produce brace-enclosed results.

Master Excel lookups and references by using index and match in array formulas to retrieve salaries from concatenated full names, with exact matches and Ctrl+Shift+Enter.

Learn to transpose data in Excel with an array formula using the Transpose function, flipping rows and columns to create a linked, dynamic transpose that updates with the original data.

Learn to combine index, match, and transpose in an array formula to look up customer details by customer ID and return a transposed result.

Use VLOOKUP with named arrays to hide the lookup table from view and perform exact-match lookups with department data and phones as a constant reference.

Master the offset function in Excel by learning how to reference a cell and return the value offset by rows and columns, including positive or negative offsets.

Explore how the offset function creates a dynamic sum range in Excel by dynamically referencing the cell above and left as you add numbers.