The INDIRECT function can be used to return a range to the VLOOKUP formula. Andy provides this example where the sheet is different for every row, but he doesn’t want to change the formula manually.
I could see this being useful when the lookup value determines which page the data is on. For example, if you had your inventory on several sheets in a workbook and the inventory item number told you which page it was on. A furniture manufacturer has two classes of inventory, desks and chairs. All the desks start with ‘DK’ and the chairs start with ‘CR’. He could still keep his inventory on two pages and use INDIRECT to point his VLOOKUP to the right list.
With only two inventory classes, there may be a simpler way to do this, but if there were a dozen, this would be a nice shortcut.