Forum Discussion
Layton__E
Oct 17, 2023Copper Contributor
Getting excel to lookup when there are multiple table options
Hi all - working on an excel file to be used at work (hopefully by coworkers as well as myself) and a bit stuck on how to write a formula for this. I have attached an example workbook with simplified table values and headers, but basically I have multiple different tables, and I am wanting excel to be able to lookup values in a specific user defined table from the multiple options. So for example, on one line they may need to reference table 1, but on the next line they could need to reference table 3. All the tables have the same headers and would be looking up the same thing, however the user will be setting these initial values (quantity and box size headers). I'm thinking I need to use a combination of if then and lookup in the formula but not knowledgeable enough to create it on my own and couldn't find any similar issues online. Any help is greatly appreciated!
- OliverScheurichGold Contributor
=INDEX(Box_size,SMALL(IF(H6<=INDIRECT(G6),ROW(A1:A7)),1))
You can try this formula along with a few named ranges. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.