Forum Discussion
formulas to search more than one worksheet
HELP! I have a spreadsheet that the formulas are working to search in another worksheet, but I had to add a second worksheet that I need the formulas to search as well.
This formulas is used for most of the info with the only info changing is the cell/row below in green.
=IFERROR(OFFSET('Product List-DOMESTIC'!$A$1,MATCH(1,INDEX(('NEW OFFICE COPY'!$E$3='Product List-DOMESTIC'!$A$2:$A$771)*('NEW OFFICE COPY'!$C$6='Product List-DOMESTIC'!$D$2:$D$771),,),0),MATCH('NEW OFFICE COPY'!$B$11,'Product List-DOMESTIC'!$A$1:$CB$1,0)-1,1,1),"-") In addition to looking in Product List-DOMESTIC, I need it to also look in Product List ITL_Bulk for the same info.
On the attached spreadsheet, if you go to cell E3 and choose the drop down the 1st 5 options are on Product List-DOMESTIC tab. The last two options CAN.ITL and BULK are on Product List ITL_Bulk tab. Once you choose a BRAND, then you need to go to cell C6 and choose a variety to pull in info. Everything is currently working for the 1st 5 options under BRAND, but I need HELP to modify all my formulas to be able to search the two worksheets. If there is a better formula option to accomplish what I need, I am open to any suggestions! Thanks!!
You'd simplify things a lot if you copied/pasted the contents of the Product List ITL_BULK sheet below the contents of the Product List-DOMESTIC sheet.
If that is not feasible, you can change the formula in J2 to
=IF(OR($E$3={"CAN.INTL","BULK"}),IFERROR(OFFSET('Product List ITL_Bulk'!$A$1,MATCH(1,INDEX(($E$3='Product List ITL_Bulk'!$A$2:$A$771)*($C$6='Product List ITL_Bulk'!$D$2:$D$771),,),0),MATCH($I$2,'Product List ITL_Bulk'!$A$1:$CB$1,0)-1,1,1),"-"),IFERROR(OFFSET('Product List-DOMESTIC'!$A$1,MATCH(1,INDEX(($E$3='Product List-DOMESTIC'!$A$2:$A$771)*($C$6='Product List-DOMESTIC'!$D$2:$D$771),,),0),MATCH($I$2,'Product List-DOMESTIC'!$A$1:$CB$1,0)-1,1,1),"-"))
and similar for the rest...
2 Replies
You'd simplify things a lot if you copied/pasted the contents of the Product List ITL_BULK sheet below the contents of the Product List-DOMESTIC sheet.
If that is not feasible, you can change the formula in J2 to
=IF(OR($E$3={"CAN.INTL","BULK"}),IFERROR(OFFSET('Product List ITL_Bulk'!$A$1,MATCH(1,INDEX(($E$3='Product List ITL_Bulk'!$A$2:$A$771)*($C$6='Product List ITL_Bulk'!$D$2:$D$771),,),0),MATCH($I$2,'Product List ITL_Bulk'!$A$1:$CB$1,0)-1,1,1),"-"),IFERROR(OFFSET('Product List-DOMESTIC'!$A$1,MATCH(1,INDEX(($E$3='Product List-DOMESTIC'!$A$2:$A$771)*($C$6='Product List-DOMESTIC'!$D$2:$D$771),,),0),MATCH($I$2,'Product List-DOMESTIC'!$A$1:$CB$1,0)-1,1,1),"-"))
and similar for the rest...
- Yes, I had it all on one worksheet previously, but I needed to restrict who could edit info and didn't want the people who used the int'l/bulk items to potentially corrupt the data on the Domestic tab.
Thanks for the formula! It works as needed! I was close, but didn't have it quite right. Thanks for the assist.
