Hi community,

I’m working on a formula that searches a range of cells and outputs it another range of cells where the criteria are based on matches on two dropdown menus which are dependent (Columns A and B)

Summarized the formula does the following:

1. Lookup: Excel searches for a selected item from a corresponding dropdown menu in column B.

2. Retrieving Previous Data: When a match is identified (two identical items), Excel should search the designated columns to retrieve the previously entered data for that item.

3. Order of Retrieval: If there are multiple entries for the same item, Excel will prioritize the nearest entry relative to a reference row (This means below the reference row, it should not search upwards). For example, if the reference row is 11, and there are entries in rows 16 and 21 for the same item, Excel should retrieve the data from row 16. (You can test this by either blanking dropdown cells in columns A and B, and it will switch to the output location)

My problem is that I need this type of functionality across different sub-sheets so that when it starts searching in let’s say week 1 (sheet 1) it will continue its search parameters in week 2 (sheet 2) and maintain the order of retrieval as mentioned in point 3 above. Essentially linking different sub-sheets together through one formula.

I attached a sample with annotations to explain how the formula functions and how I need it to work.

The solutions I can think of are;

1. Simplyfing the formula making it less stress intensive so that it would be possible to merge all sub-sheets into one big sheet

2. Creating a master sheet where one can perform lookups using the INDIRECT and MATCH functions

I’m not sure how to fully flesh out these ideas or of other solutions however.

​Hi community,I’m working on a formula that searches a range of cells and outputs it another range of cells where the criteria are based on matches on two dropdown menus which are dependent (Columns A and B)Summarized the formula does the following:1. Lookup: Excel searches for a selected item from a corresponding dropdown menu in column B.2. Retrieving Previous Data: When a match is identified (two identical items), Excel should search the designated columns to retrieve the previously entered data for that item.3. Order of Retrieval: If there are multiple entries for the same item, Excel will prioritize the nearest entry relative to a reference row (This means below the reference row, it should not search upwards). For example, if the reference row is 11, and there are entries in rows 16 and 21 for the same item, Excel should retrieve the data from row 16. (You can test this by either blanking dropdown cells in columns A and B, and it will switch to the output location)=IF(B11=””,””,IFERROR(MAKEARRAY(5,3,LAMBDA(x,y,INDEX($G$1:$I$25,XLOOKUP(A11&B11,$A$5:$A$10&$B$5:$B$10,ROW($B$4:$B$9),,0,-1)+x,y))),””))My problem is that I need this type of functionality across different sub-sheets so that when it starts searching in let’s say week 1 (sheet 1) it will continue its search parameters in week 2 (sheet 2) and maintain the order of retrieval as mentioned in point 3 above. Essentially linking different sub-sheets together through one formula.I attached a sample with annotations to explain how the formula functions and how I need it to work.The solutions I can think of are;1. Simplyfing the formula making it less stress intensive so that it would be possible to merge all sub-sheets into one big sheet2. Creating a master sheet where one can perform lookups using the INDIRECT and MATCH functionsI’m not sure how to fully flesh out these ideas or of other solutions however.  Read More

​