In our scenario we need to apply discounts to product sales based on quantity sold and product category. The discount information is held in different category tables so when we apply a discount it must pull information from the correct table.
One solution to this is to use the CHOOSE function which allows you to specify an index number and then a series of values. If index number 1 is specified, value 1 is returned, an index equal to 2, would return value 2 etc.
So we can link each product category with an index number with a simple VLOOKUP. 1 =A, 2=B, 3=C. Now we can specify the VLOOKUP to perform in each value argument.
=CHOOSE(VLOOKUP(B9,INDEX,2,FALSE),VLOOKUP(C9,CATA,2),VLOOKUP(C9,CATB,2),VLOOKUP(C9,CATC,2))
The video shows you how to do this. It also features the MID function and how to convert ranges to named Excel Tables which comes in handy when you need to refer to lookup tables.