Application of Excel XLOOKUP, HLOOKUP, VLOOKUP, INDEX-MATCH

Explore essential Excel basics, including selecting and highlighting cells, resizing columns, merging and centering, and formatting with colors and fonts. Practice navigation, autofill with fill handle, and managing worksheets.

Learn practical Excel copy-paste techniques, including regular paste, keep source formatting, keep source column width, transpose, and paste values only, plus number formatting and decimal control using Ctrl+1.

Learn to use the Excel search bar in Office 2021 to quickly locate tools such as font changes, sorting, justification, and date formatting with Alt Q, boosting navigation and precision.

Learn to use Excel's find and replace with ctrl f, choosing replace all or replace one by one. Explore wildcards—begins with, ends with, contains—and the asterisk and question mark.

Learn how to rapidly highlight and fill Excel ranges using click-and-drag, ctrl a, shift keys, and ctrl shift arrows, and how double-clicking the fill handle extends data to the end.

Master Excel data validation to enforce numbers, dates, times, and text length using between, greater than, less than, and equal to, with custom input messages and error alerts.

Create and apply a list in Excel using data validation, selecting a source range or typing the source, and use circle invalid data and clear validation.

Learn basic arithmetic in Excel: write formulas starting with an equals sign using plus, minus, multiplication, division, and power. Use the formula bar, cell references, and brackets to combine operations.

Master logical operators in Excel formulas, including greater than, less than, not equal to, and equal to, with guidance on using quotes for text and starting formulas with equals.

Learn to concatenate in Excel using the ampersand to join numbers and text in formulas, with quotes for text and spaces where needed.

Explore absolute versus relative referencing in Excel, using the name box, fill down techniques, and the dollar sign to lock rows or columns for stable references.

Master absolute and relative referencing in Excel by using fill and drag, locking cells with dollar signs to fix rows and columns, and applying fixed rates across multiple cells.

Explains how to build a spill range multiplication table in Excel, using spill range references with hash and blue borders, and notes compatibility with Office 365 and Office 2021.

Master writing array constants in Excel with curly braces and quotes, and build arrays in rows or columns. Explore spill ranges and array arithmetic, including multiplying arrays.

Explore array formulas in Excel, performing arithmetic and logical comparisons on arrays, testing equality, and learning when to use Ctrl+Shift+Enter vs Enter to create spill and non-spill arrays.

Use index to retrieve a value at the intersection of a row and column in a range. Use match to locate a value’s position with exact matching (zero) for lookups.

explain the excel match function and match type, including default 1 (less than or approximately), exact match 0, and -1 (greater than), with lookup values and arrays.

Learn how to nest a match inside an index function to retrieve hourly data, compare nesting with a non-nested index, and prepare for two matches in one index function.

Learn to combine index and match to retrieve item prices by product and plan in a practical store, using a two-way index–match approach for items, plans, sizes, and data validation.

Discover the choose function, which selects an item by index from a list, and the transpose function, which uses arrays to swap rows and columns in Excel’s lookup and reference tools.

Explore how column and row reference functions work in Excel, using column, row, columns, and rows to identify current positions and count ranges.

Learn how the lookup and vlookup functions retrieve data using a lookup vector, a result vector, a table array, and a column index number with exact or approximate matches.

Learn to use VLOOKUP to retrieve ratings, comments, and levels from a separate table via vertical lookup, selecting the table array and column index with an exact-match option.

Learn to nest the match function inside vlookup to fetch department details (job type, date, salary, job rating, city or zip) with exact matching using data validation lists.

Learn how the HLOOKUP function performs horizontal lookups, compares with VLOOKUP, and uses a table array and row index to retrieve exact matches.

Lock the table array to make it absolute in hlookup, enabling reliable replication across cells; use F4 or dollar signs to keep the range constant for exact lookups.

Learn to combine hlookup with match to retrieve values from horizontal details, and contrast with vlookup using match, navigating vertical departments and horizontal headers for salary or zip codes.

Learn to use VLOOKUP to find duplicate values across two data arrays, and compare exact versus approximate matches with error handling.

Combine vlookup and hlookup to query data across vertical and horizontal axes in a single table, using hlookup for the column index and vlookup for the result, with exact matching.

Combine vlookup and hlookup to create dynamic, data-validated lookups across vertical and horizontal tables. Use exact and range lookups, table arrays, and row and column index numbers.

discover how the address function returns a text cell reference from a row and column, with absolute or relative options and A1 or R1C1 styles.

Explore the Excel offset function, learning how to return a dynamic range by offsetting a reference with rows and columns, including zero-based counting, negative shifts, and height and width options.

Master hyperlink, formula text, and indirect in Excel to link locations, convert formulas to text, and perform lookups and references with r1c1 style, using vlookup and index-match in practical projects.

Map item prices across multiple stores using VLOOKUP and INDIRECT, defining named ranges for Denmark, Canada, and Belarus, and dynamically retrieving prices by region and item with exact-match lookups.

Explore mapping data with vlookup and indirect to pull regional sales by staff, using data validation and named ranges for West, East, South, and North.

Demonstrate how VLOOKUP and INDIRECT map text strings to dynamic cell references using the evaluate formula, revealing the underlying lookup results and how text becomes references.

Combine VLOOKUP with RANDBETWEEN to generate random data from a table array, using a serial number as the lookup anchor and selecting the desired column from the table.

Learn to combine vlookup and randbetween to generate random data, using array constants to spill results across multiple columns or rows while mapping values to their counterparts.

Learn to generate random data in Excel using INDEX and RANDBETWEEN, selecting from a table array by row and column, and when a serial number is needed.