Excel 3 Ways to Lookup within Boundaries: SUMIFS, SUMPRODUCT & INDEX
Feb 13, 2017 Excel Lookup Formulas
Check the Excel Essentials Course: https://courses.xelplus.com/p/learn-excel-essentials
Three powerful Excel Formulas that lookup values in ranges, i.e. ranges that include lower bound and upper bound values.
Method 1: First method uses the SUMIFS formula with greater than and smaller than as criteria.
Method 2: SUMPRODUCT formula works great here too as it can work well with exceptions (and condition)
Method 3: The next method uses INDEX and Match function to find the category that coincides with the defined boundary values. Instead of looking for the exact match, we will use the option "less than" in the match type argument.
Method 4: A fourth option that came to my mind when I was creating the thumbnail for the video was the LOOKUP formula:
=LOOKUP(B2,$H$2:$H$7,$G$2:$G$7)
Blogpost: http://www.xelplus.com/lookup-within-boundaries/
★ My Online Excel Courses ► https://courses.xelplus.com/
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
-
54:17
Microsoft Excel Full Course
3 years agoExcel 365 Part 12 – Lookup Functions
71 -
6:52
OATtech
2 years agoFuzzy LookUp in Excel
52 -
13:12
Excel 2 the world
7 months agoMicrosoft Excel Functions- SUM & AUTOSUM
15 -
10:24
RahimZulfiqarAli
1 year agoTwo Dimensional Row-and-Column LOOKUP in Microsoft Excel
4 -
1:42
syeduddin1786
1 year agoUsing Sumproduct Formula in Excel.
12 -
2:13
DansTechSolutions
1 year agoHow To Create A SUMIF Statement In Excel - Excel Tutorial
12 -
0:16
Mighty Excel
3 years agoHow to auto sum in Ms Excel
3 -
9:26
Microsoft Excel Full Course
3 years agoExcel 365 Part 22 – What-If Analysis
85 -
13:26
user329
3 years agoVLOOKUP FUNCTION IN EXCEL / SPREADSHEET
30 -
3:54
OfficeBeginners
2 years agoHow to use autosum for addition in excel
23