With Table1 and Table2 as indicated in the screenshot. Where the MATCH function is used to find the correct row to return from array, and the INDEX function returns the value at that array. However, in this case we want to make the array variable, so that the range given to INDEX can be changed on the fly. We do this with the CHOOSE function: The CHOOSE function returns a value from a list using a given position or index. The value can be a constant, a cell reference, an array, or a range. In the example, the numeric index is provided in column H. When the index number is 1, we use Table1. When the index is 2, we feed Table2 to INDEX: Note: the ranges provided to CHOOSE don’t need to be tables, or named ranges. In I5, the number in column H is 1, so CHOOSE returns Table1, and the formula resolves to: The MATCH function returns the position of “A” in Table1, which is 1, and INDEX returns the value at row 1, column 2 of Table1, which is $20.00
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
