Excel Magic Trick 1100

Cross Tabulated Lookup: 1) Lookup Row then match or 2) Array Multiplication?


  • See Two Methods that can return a "Yes" when a Two Way Lookup based on a Column Header and Row Header must lookup items from a second table that presents Dimension (row / column size) Problems.

    Method 1:
    INDEX to lookup a row based on a column header that can then be matched against a row header. See the functions: INDEX, MATCH, ISNUMBER and IF. Learn about array syntax. Learn that although the evaluated INDEX function looks like it returns an array, it actually returns a range that the MATCH function can understand.

    Method 2:
    Array Comparative Operation and Array Multiplication. See the functions SUMPRODUCT and IF. Learn about array syntax.

    Lookup based on row and column headers. Two Way Lookup.