Uploaded December 2015 | Updated September 2026, 2 hours ago

---

Get Excel file 00141 here: http://1drv.ms/1bYwrTa

(00:00) Audit Formula: =VLOOKUP(B1,INDIRECT(VLOOKUP(A1,Data1!A6:B9,2,FALSE)),2,FALSE)
(00:28) Isolate the difficult part: table_array
(01:15) See the result of the inner vlookup (Sweden's range)
(02:28) Result leads to Sweden's mini range and to favorite sport.
---------now we audit data layout & create an easier solution--------
(03:42) Complex formula due to poor data layout!
(04:04) Hard coded references are dangerous! Leads to errors.
(04:29) Is there a better or safer solution?
(04:33) Add named ranges? Not a good idea.
(06:00) SOLUTION! Normalize data, insert table, add formula.

Cheers,
Kevin Lehrbass
twitter.com/kevinlehrbass
myspreadsheetlab.com/blog