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.

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.

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.

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.

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 use VLOOKUP to find duplicate values across two data arrays, and compare exact versus approximate matches with error handling.

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.

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.