digestVidSign in
summary

excelisfun - Excel Magic Trick 1174: Two Lookup Values For VLOOKUP? How Do I Do That? (6 Methods)

Thumbnail of the video “excelisfun - Excel Magic Trick 1174: Two Lookup Values For VLOOKUP? How Do I Do That? (6 Methods)”
Community upload. AI output has not been independently verified.
Author: excelisfun
Categories: Technology Science
Tags: technology, science
Views: 2 · Last viewed: 2026-10-16 00:54 UTC

Short Summary

Main recommendation: To look up a match on two criteria in Excel, the video's favorite single-cell method is INDEX MATCH entered with Ctrl+Shift+Enter, while a helper join column with VLOOKUP is the simplest option when you can add a column. The video presents five methods for text lookups plus one simple SUMIFS approach for numeric results without duplicates (six methods total).

Key Points

  • The six methods, as presented: (1) add a helper join column and use VLOOKUP; (2) MATCH inside INDEX with Ctrl+Shift+Enter; (3) INDEX wrapping the array calculation with an empty row number, which avoids Ctrl+Shift+Enter; (4) VLOOKUP with CHOOSE to build a two-column table, entered with Ctrl+Shift+Enter; (5) DGET when the data has field names at the top and criteria set up with matching field names; (6) SUMIFS for numeric values when there are no duplicates.
  • VLOOKUP's table_array argument will not handle the joined array calculation directly, which is why a helper column or CHOOSE is needed.
  • IFERROR (Excel 2007 and later) or IFNA (Excel 2013 and later) can replace errors with a message such as "no match" or a blank string.
  • The array functions that handle array operations without Ctrl+Shift+Enter are INDEX, LOOKUP, SUM, and PRODUCT.
  • SUMIFS works for a single lookup only when no duplicate combinations exist, because it adds matching values rather than retrieving one.

Details Worth Noting

  • Concrete instructions: For the helper-column method, create a join column with =city & store name, fill it down, then use =VLOOKUP(lookup1 & lookup2, table, column_index, 0). For INDEX MATCH, enter =INDEX(return_range, MATCH(city & store, city & store range, 0)) and press Ctrl+Shift+Enter. For the no-CSE method, place the array calculation inside INDEX and leave the row number empty. For the CHOOSE method, use VLOOKUP with CHOOSE({1,2}, joined_range, value_range) as the table, column index 2, exact match, and Ctrl+Shift+Enter. For DGET, the database must have field names in the top row and records below; criteria use the same field names. For SUMIFS, use sum range, then criteria range and criteria pairs for each lookup value.
  • The video recommends wrapping any of these in IFERROR or IFNA to handle missing matches.
  • The video states that if you have a proper data set with matching field names, DGET is the best of the methods shown.
  • The video notes that SUMIFS will sum rather than retrieve if duplicates exist.

Bottom Line

When the lookup needs two criteria, the video's preferred choice depends on your setup: a helper column for simplicity, INDEX MATCH for a single cell, or DGET for a clean database layout.