Group numbers with VLOOKUP in Excel

February 5, 2019 by exceloffice Leave a Comment

This tutorial shows how to Group numbers with VLOOKUP in Excel using the example below;

Formula

=VLOOKUP(value,group_table,column,TRUE)

Explanation

If you need to group by number, you can use the VLOOKUP function with a custom grouping table. This allows you to make completely custom or arbitrary groups.

In the example shown, the formula in F7 is:

=VLOOKUP(D5,age_table,2,TRUE)

How this formula works

This formula uses the value in cell D5 for a lookup value, the named range “age_table” (G5:H8) for the lookup table, 2 to indicate “2nd column”, and TRUE as the last argument indicate approximate match.

Also See: VLOOKUP by date in Excel

Note: the last argument is optional, and defaults to TRUE, but I like to explicitly set the matching mode.

VLOOKUP simply looks up the age and returns the group name from the 2nd column in the table. This column can contain any values that you wish.

Pivot tables

Pivot tables can automatically group numbers, but the VLOOKUP approach allows you to perform completely custom grouping.

Also See: Create hyperlink with VLOOKUP in Excel

Related Posts:

Leave a Reply Cancel reply

A -Z Excel Functions and Formulas Example

ACCRINT function

ACCRINTM function

ADDRESS function

AGGREGATE function

AMORDEGRC function

AMORLINC function

Analysis ToolPak

ARABIC function

AVEDEV function

AVERAGE function

AVERAGEA function

AVERAGEIF function

AVERAGEIFS function

BAHTTEXT function

BESSELI function

BESSELJ function

BESSELK function

BESSELY function

BETA.DIST function

BETA.INV function

BIN2DEC function

BIN2HEX function

BIN2OCT function

BINOM.DIST function

BINOM.DIST.RANGE function

BINOM.INV function

BITAND function

BITLSHIFT function

BITRSHIFT function

BITXOR function

CEILING function

CEILING.MATH function

CEILING.PRECISE function

Cell Ranges & References

CHISQ.DIST function

CHISQ.DIST.RT function

CHISQ.INV function

CHISQ.INV.RT function

CHISQ.TEST function

CHOOSE function

Clustered Column

COLUMN function

COLUMNS function

COMBIN function

COMBINA function

COMPLEX function

Compound Annual Growth Rate

CONCAT function

CONCATENATE function

Conditional Formatting

CONFIDENCE.NORM function

CONFIDENCE.T function

CONVERT function

CORREL function

COUNTA function

COUNTBLANK function

COUNTIF function

COUNTIFS function

COUPDAYBS function

COUPDAYS function

COUPDAYSNC function

COUPNCD function

COUPNUM function

COUPPCD function

COVARIANCE.P function

COVARIANCE.S function

CUBEKPIMEMBER function

CUBEMEMBER function

CUBEMEMBERPROPERTY function

CUBERANKEDMEMBER function

CUBESET function

CUBESETCOUNT function

CUBEVALUE function

CUMIPMT function

CUMPRINC function

Custom Number Format

DATEDIF function

DATEVALUE function

DAVERAGE function

DAYS360 function

DCOUNT function

DCOUNTA function

DEC2BIN function

DEC2HEX function

DEC2OCT function

DECIMAL function

DEGREES function

DOLLAR function

DOLLARDE function

DOLLARFR function

DPRODUCT function

DSTDEV function

DSTDEVP function

DURATION function

EFFECT function

ENCODEURL function

EOMONTH function

ERFC.PRECISE function

ERROR.TYPE function

EXPON.DIST function

F.DIST function

F.DIST.RT function

F.TEST function

FACTDOUBLE function

FILTERXML function

FISHER function

FISHERINV function

FLOOR.MATH function

FLOOR.PRECISE function

FORECAST function

Formulas and Functions

FORMULATEXT function

FREQUENCY function

FVSCHEDULE function

GAMMA.DIST function

GAMMA.INV function

GAMMALN function

GAMMALN.PRECISE function

GEOMEAN function

GESTEP function

GETPIVOTDATA function

GROWTH function

HARMEAN function

HEX2BIN function

HEX2DEC function

HEX2OCT function

Histogram Chart

HLOOKUP function

HYPERLINK function

HYPGEOM.DIST function

IFERROR function

IMAGINARY function

IMARGUMENT function

IMCONJUGATE function

IMCOSH function

IMCSCH function

IMLOG10 function

IMLOG2 function

IMPOWER function

IMPRODUCT function

IMREAL function

IMSECH function

IMSINH function

IMSQRT function

INDIRECT function

INTERCEPT function

INTRATE function

ISBLANK function

ISERROR function

ISEVEN function

ISFORMULA function

ISLOGICAL function

ISNONTEXT function

ISNUMBER function

ISO.CEILING function

ISOWEEKNUM function

ISTEXT function

LINEST function

LOGEST function

LOGNORM.DIST function

LOGNORM.INV function

LOOKUP function

MAXIFS function

MDETERM function

MDURATION function

MEDIAN function

MINIFS function

MINUTE function

MINVERSE function

MODE.MULT function

MODE.SNGL function

MROUND function

MULTINOMIAL function

NEGBINOM.DIST function

Net Present Value

NETWORKDAYS function

NETWORKDAYS.INTL function

NOMINAL function

NORM.DIST function

NORM.INV function

NORM.S.DIST function

NORM.S.INV function

NUMBERVALUE function

OCT2BIN function

OCT2DEC function

OCT2HEX function

ODDFPRICE function

ODDFYIELD function

ODDLPRICE function

ODDLYIELD function

OFFSET function

Operator Precedence

PDURATION function

PEARSON function

PERCENTILE function

PERCENTILE.EXC function

PERCENTILE.INC function

PERCENTRANK.EXC function

PERCENTRANK.INC function

PERMUT function

PERMUTATIONA function

PHONETIC function

POISSON.DIST function

PRICEDISC function

PRICEMAT function

PRODUCT function

PROPER function

QUARTILE function

QUARTILE.EXC function

QUARTILE.INC function

QUOTIENT function

RADIANS function

RANDBETWEEN function

RANK.AVG function

RANK.EQ function

RECEIVED function

REPLACE function

REPLACEB function

RIGHTB function

ROUNDDOWN function

ROUNDUP function

SEARCH function

SEARCHB function

SECOND function

SEQUENCE function

SERIESSUM function

SHEETS function

SKEW.P function

Sort and Filter

SQRTPI function

STANDARDIZE function

STDEV.S function

STDEVA function

STDEVPA function

SUBSTITUTE function

SUBTOTAL function

SUMIFS function

SUMPRODUCT function

SUMX2MY2 function

SUMX2PY2 function

SUMXMY2 function

SWITCH function

T.DIST function

T.DIST.2T function

T.DIST.RT function

T.INV.2T function

T.TEST function

TBILLEQ function

TBILLPRICE function

TBILLYIELD function

TEXTJOIN function

Thermometer Chart

TIMEVALUE function

TRANSPOSE function

TRIMMEAN function

UNICHAR function

UNICODE function

Unix Time Stamp

VLOOKUP function

WEBSERVICE function

WEEKDAY function

WEEKDAY.INTL function

WEEKNUM function

WEIBULL.DIST function

What-If Analysis:

WORKDAY function

WORKDAY.INTL function

YEARFRAC function

YIELDDISC function

YIELDMAT function

Z.TEST function