Looking up and extracting data from tables of numbers and strings is one of those things that most serious data analysts need to do on a regular basis. If you’re familiar with Excel, you may have come across VLOOKUP and HLOOKUP and more recently XLOOKUP. Each of these functions helps with a particular look up task. XLOOKUP is the most recent and is getting a lot of people excited.
A more basic dynamic lookup can be achieved by combining INDEX and MATCH. @msexcel have highlighted this approach today.
It works by nesting a use of MATCH inside a INDEX. Nesting this way sometimes gets confusing. The EQUS add-in is great for just helping see a bit more of what’s going on when you do this. Here’s our little example.
MATCH and INDEX example