This video will show how to achieve two things in the context of using Lookup functions such as VLOOKUP, LOOKUP, XLOOKUP, INDEX & MATCH.  You can download the featured files here.

XLOOKUP Version – requires Office 365 or Excel Online.

Non XLOOKUP Version – works in any version of Excel.

Sum all matching values

Here we want to calculate the total cost of our shopping list.  The total comes to £124 – but how do you calculate this in a single cell?  We will look at how to do this with XLOOKUP and then LOOKUP with SUMPRODUCT. The XLOOKUP version of the formula is =SUM(XLOOKUP(E2:E5,A2:A24,B2:B24))

The LOOKUP version of the formula is =SUMPRODUCT(LOOKUP(E2:E5,A2:B24))

Sum all values contained in the matching row or column

Here we want to add up the sales in the matching column.  The video shows you how to do this with XLOOKUP and then with HLOOKUP & SUMPRODUCT and then with INDEX, MATCH & SUM. The XLOOKUP version of the formula is =SUM(XLOOKUP(I11,J2:S2,J3:S8))

The HLOOKUP version of the formula is =SUMPRODUCT(HLOOKUP(I11,J2:S8,{2,3,4,5,6,7},0))

The INDEX version of the formula is =SUM(INDEX(J3:S8,,MATCH(I11,J2:S2,0)))

LIVE ONLINE TRAINING COURSES WITH A LIVE TUTOR - AVAILABLE WORLDWIDE

Private or public courses available:
ONLINE EXCEL TRAINING (LIVE TUTOR): See Our Training Dates - Excel Fundamentals, Intermediate, Advanced and VBA Courses.

ONLINE MS ACCESS TRAINING (LIVE TUTOR): See Our Training Dates
ONLINE MS PROJECT TRAINING (LIVE TUTOR): See Our Training Dates
ONLINE POWERPOINT TRAINING (LIVE TUTOR): See Our Training Dates

VIDEO TRAINING - LEARN AT YOUR OWN PACE

DEEP DIVE INTO LOOKUP FUNCTIONS - XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX, IFERROR, ISNA, IFNA, LEFT, RIGHT, MID, FIND, SEARCH, LEN, SUBSTITUTE, REPLACE, TRIM, CLEAN & INDIRECTClick here to enrol

DEEP DIVE INTO DATE FUNCTIONS - TODAY, DATEDIF, WORKDAY, NETWORKDAYS, EOMONTH, EDATE, DATE, DATEVALUE, DAY, MONTH, YEAR, WEEKNUM, ISOWEEKNUM, WEEKDAY & YEARFRAC.