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.
Click here to enrol

THE MS ACCESS FOR BEGINNERS' COURSE - Click here to enrol


FREE STUFF
70+ MUST KNOW EXCEL SHORTCUT KEYS: Download the pdf
UNDERSTAND & FIX EXCEL ERRORS: Download the pdf 

 

 

 

Posted by Blue Pecan Computer Training