Hi everyone,
I’m having difficulty managing a large file with many lookups, and cannot create a formula for a specific lookup I need. I have created a similar situation example which is simpler to better understand the problem. I am using 2 separate workbooks (2 separate tables) and trying to lookup a value from one and use it in the other.
Table 1 is where the raw data is; it is a download/extract from another program and is used by many people and therefore cannot be changed or reformatted for ease of use. It must be used as is. Also note that the blank cells are not actually empty and excel reads them as such, meaning a “ISBLANK” formula will not work for this problem.
Table 1 looks like this:
<tbody>
</tbody>
The headers describe ingredients in recipes, and numbers in column A represent different recipes. Where it says “Yes” indicates that that ingredient is in the recipe.
Table 2 looks like this:
<tbody>
</tbody>
This is the working file where the same recipes are listed in column A, but will be in a varying order. As new recipes are created, they will be added and so this must be a continuous file. The headers in this file give a list of grouped ingredients, the same list for 5 columns (a recipe can have a max of 5 of the ingredients in this list, however there are more than 5 ingredients possible). Each recipe needs to indicate which ingredients are in it. In this example, recipe 3 has lemons. If it contained more than this, it would list them in no particular order in 5 cells for that recipe.
I need a formula which reads the header from Table 1, from the same column as “Yes” for the corresponding recipe. Here, Table 1 says ‘Yes’ under lemons from column E for recipe 3, so Table 2 says lemons under the first available spot for ingredients.
My approach is this: I created an INDEX MATCH MATCH formula to find the header. I used the entire Table 1 as the array. The row will always be 1 (as the headers will always appear in this row). The column number of the header will match the column number of the work “Yes”, however I cannot seem to make this work. My thought is that I would match the word ‘Yes’ within the recipe number row, and return the column number. Keep in mind, since this is a large file and this same type of sequence occurs many times, it needs to specifically look in columns B-K only, as the work ‘Yes’ will exist elsewhere in the file for that row. Any help would be greatly appreciated !!
Thanks
I’m having difficulty managing a large file with many lookups, and cannot create a formula for a specific lookup I need. I have created a similar situation example which is simpler to better understand the problem. I am using 2 separate workbooks (2 separate tables) and trying to lookup a value from one and use it in the other.
Table 1 is where the raw data is; it is a download/extract from another program and is used by many people and therefore cannot be changed or reformatted for ease of use. It must be used as is. Also note that the blank cells are not actually empty and excel reads them as such, meaning a “ISBLANK” formula will not work for this problem.
Table 1 looks like this:
| Recipe | Apple | Pear | Grape | Lemon | Orange | Lime | Banana |
| 6 | |||||||
| 2 | |||||||
| 1 | |||||||
| 5 | |||||||
| 3 | Yes | ||||||
| 4 | |||||||
| 7 |
<tbody>
</tbody>
The headers describe ingredients in recipes, and numbers in column A represent different recipes. Where it says “Yes” indicates that that ingredient is in the recipe.
Table 2 looks like this:
| Recipe | Apple Pear Grape Lemon Orange Lime Banana | Apple Pear Grape Lemon Orange Lime Banana | Apple Pear Grape Lemon Orange Lime Banana | Apple Pear Grape Lemon Orange Lime Banana | Apple Pear Grape Lemon Orange Lime Banana | Other | Other |
| 1 | |||||||
| 2 | |||||||
| 3 | Lemon | ||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 |
<tbody>
</tbody>
This is the working file where the same recipes are listed in column A, but will be in a varying order. As new recipes are created, they will be added and so this must be a continuous file. The headers in this file give a list of grouped ingredients, the same list for 5 columns (a recipe can have a max of 5 of the ingredients in this list, however there are more than 5 ingredients possible). Each recipe needs to indicate which ingredients are in it. In this example, recipe 3 has lemons. If it contained more than this, it would list them in no particular order in 5 cells for that recipe.
I need a formula which reads the header from Table 1, from the same column as “Yes” for the corresponding recipe. Here, Table 1 says ‘Yes’ under lemons from column E for recipe 3, so Table 2 says lemons under the first available spot for ingredients.
My approach is this: I created an INDEX MATCH MATCH formula to find the header. I used the entire Table 1 as the array. The row will always be 1 (as the headers will always appear in this row). The column number of the header will match the column number of the work “Yes”, however I cannot seem to make this work. My thought is that I would match the word ‘Yes’ within the recipe number row, and return the column number. Keep in mind, since this is a large file and this same type of sequence occurs many times, it needs to specifically look in columns B-K only, as the work ‘Yes’ will exist elsewhere in the file for that row. Any help would be greatly appreciated !!
Thanks
