To perform a lookup on multiple criteria you will need to concatenate (join up) your lookup values and concatenate the corresponding columns in your lookup table. This video tutorial looks at three ways you can achieve this. There are some notes below the the video to help you understand, but really you need to watch the tutorial and download the featured file here.
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.
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 & INDIRECT. Click 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
XLOOKUP is by far the easiest method, but you will need an Office 365 subscription or Excel Online to use it . XLOOKUP is the easiest method because it allows you to concatenate both in the lookup_value argument and the lookup_array argument. Here’s the XLOOKUP formula featured in the video.
VLOOKUP with Helper Column
Using this method you need to create a helper column in your lookup table that concatenates the values that you are looking up.
You can then use the helper column as the first column in your lookup table. The VLOOKUP formula featured in the video looks like this:
VLOOKUP with CHOOSE
Using this option you won’t need a helper column in your lookup table but you will need to press CTRL SHIFT ENTER to confirm your formula (not true for Office 365 users – but you should be using XLOOKUP anyway). The CHOOSE function allows you to construct a lookup table within the formula, the first column of which is a concatenation of the columns that you are cross referencing, the second column being the values you want to return.
Notice the brace brackets – this tells you its an array formula. Array formulas are created by confirming with CTRL SHIFT ENTER.