VLOOKUP Concatenate Two Values: Master Helper Columns, CHOOSE, and XLOOKUP
Concatenating two values for VLOOKUP is a technique for combining multiple criteria, such as a product code and month, into a single combined key for an exact match. Helper columns are the most intuitive and fastest approach, and we will also cover the CHOOSE trick and XLOOKUP/FILTER alternatives.
Quick Fix — One-Line Helper Column Formula
=IFERROR(VLOOKUP($A2 & "-" & TEXT($B2,"yyyymm"), $F$2:$I$1000, 4, FALSE), "")How It Works
Because VLOOKUP supports only one lookup key, you combine the criteria into text and look them up as a single key.
Practical Example 1: Helper Column
=[@상품코드] & "-" & TEXT([@월],"yyyymm")=IFERROR(VLOOKUP($A2 & "-" & TEXT($B2,"yyyymm"), $F$2:$I$1000, 4, FALSE), "")Practical Example 2: CHOOSE Trick
=IFERROR(VLOOKUP($A2 & "-" & TEXT($B2,"yyyymm"),
CHOOSE({1,2}, $F$2:$F$1000 & "-" & TEXT($G$2:$G$1000,"yyyymm"), $I$2:$I$1000), 2, FALSE), "")Practical Example 3: Format-Safe Concatenation
=TRIM(CLEAN(UPPER([@상품코드]))) & "-" & TEXT([@월],"yyyymm")Practical Example 4: XLOOKUP and FILTER
=IFERROR(XLOOKUP($A2 & "-" & TEXT($B2,"yyyymm"),
$F$2:$F$1000 & "-" & TEXT($G$2:$G$1000,"yyyymm"), $I$2:$I$1000, ""), "")=FILTER($I$2:$I$1000, ($F$2:$F$1000=$A2)*(TEXT($G$2:$G$1000,"yyyymm")=TEXT($B$2,"yyyymm")), "")Troubleshooting
| Issue | Cause | Solution |
|---|---|---|
| #N/A | Format or spacing mismatch | Standardize with TRIM/CLEAN/UPPER and TEXT() |
| Only September fails | Digit count (1 vs. 01) | Use TEXT(month,”00″) or a date in “yyyymm” format |
| Incorrect value | Approximate match | Use FALSE as the last argument |
| Slow performance | Large CHOOSE arrays | Use a helper column, reduce the range, or use a table |
Related Articles
- Complete Guide to VLOOKUP with Multiple Criteria
- XLOOKUP Basics and How to Switch
- Data Cleaning: TRIM/CLEAN
- SUMIFS Multiple-Criteria Best Practices
- Table Basics and Benefits