Lookup & Reference Functions

VLOOKUP and HLOOKUP

VLOOKUP function VLOOKUP is one of the lookup and reference functions you can use when you need to find things in a table or a range by row. For example, look up an employee’s last [Read More >>>]

Dueling Excel

Dueling Excel 042

Lookup Picture In Excel Formula or VBA? DESCRIPTION See Mr Excel and excelisfun lookup a picture in Excel. See a VBA solution and a Formula Solution using the INDIRECT function and named ranges. See many [Read More >>>]

Dueling Excel

Dueling Excel 041

VBA or Formula Extract Prefix for VLOOKUP DESCRIPTION See Mr Excel and excelisfun do VBA and an array formula to extract a varying length number prefix and then use that as the lookup_value for VLOOKUP. [Read More >>>]

Excel Is Fun

Excel Magic Trick 1149

Dynamically Sum by Category without VLOOKUP Helper Column (2 methods) DESCRIPTION See how to: Dynamically Sum Costs by Category without VLOOKUP Helper Column: (00:47) VLOOKUP helper Column and SUMIFS function (03:10) Array Formula using SUMPRODUCT [Read More >>>]

Excel Is Fun

Excel Magic Trick 1140

VLOOKUP, TEXT & DOLLAR functions To Create Dynamic Finance Test Text Labels DESCRIPTION See how to Create Text Formulas For Finance Tests where you can change the variables PV, APR, Years and compounding periods per [Read More >>>]

Excel Is Fun

Excel Magic Trick 1137

Excel & PowerPivot DAX Formula: Approximate Match Lookup Without VLOOKUP DESCRIPTION See how to (00:08) Intro. (01:21) Approximate Match with VLOOKUP. (02:20) Approximate Match with AGGREGATE (to simulate logic that is present in DAX formula). [Read More >>>]

Dueling Excel

Dueling Excel 029

Existing Customers In Prospect List? DESCRIPTION See Mr Excel and excelisfun compare a customer list and prospects list to see if customers are in the prospects list. Then remove the duplicates. See the MATCH, VLOOKUP [Read More >>>]

Excel Is Fun

Excel Magic Trick 1134

IF or VLOOKUP function for Assigning Categories w/ Excel Formula 8 Examples DESCRIPTION See how to assign product and sales categories using various methods: Overview (00:44) Overview (01:30) IF and Hard Coded Values: Violates Excel’s [Read More >>>]