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. 

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 

 

 

XLOOKUP

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.

=XLOOKUP($G4&H$3,$A$2:$A$12&$B$2:$B$12,$D$2:$D$12,””)

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:

=IFNA(VLOOKUP($G15&H$14,$C$2:$D$12,2,0),””)

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.

{=IFERROR(VLOOKUP($G25&H$24,CHOOSE({1,2},$A$2:$A$12&$B$2:$B$12,$D$2:$D$12),2,0),””)}

Notice the brace brackets – this tells you its an array formula.  Array formulas are created by confirming with CTRL SHIFT ENTER.

Posted by Blue Pecan Computer Training