Complex Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-3113524%22%20slang%3D%22en-US%22%3EComplex%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3113524%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20can%20I%20run%20a%20formula%20that%20would%20span%20across%20multiple%20sheets%20and%20give%20me%20a%20specific%20cell%20while%20having%20multiple%20logical%20tests%20that%20it%20has%20to%20pass%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eex%3A%20if%20cells%26nbsp%3B%20Sheet%201%20A1%3AA8%20%3D%26nbsp%3B%20Sheet%202%20B1%2C%20and%20Sheet%201%20B1%3AB8%20%3D%20Sheet%202%20C1%2C%20and%20Sheet%201%20C1%3AC8%20%3D%20Sheet%202%20D1%2C%20then%20the%20cell%20adjacent%20to%20the%20matching%20value%20is%20the%20answer%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20that%20make%20any%20sense%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3113524%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3113568%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3113568%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1296610%22%20target%3D%22_blank%22%3E%40hstein1827%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20want%20to%20return%20a%20value%20from%20column%20D%20on%20Sheet%201%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX('Sheet%201'!%24D%241%3A%24D%248%2C%20MATCH(1%2C%20(Sheet%201'!%24A%241%3A%24A%248%3DB1)*('Sheet%201'!%24B%241%3A%24B%248%3DC1)*('Sheet%201'!%24C%241%3A%24C%248%3DD1)%2C%200))%2C%20%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EChange%20Sheet1%20to%20the%20real%20name%20of%20that%20sheet.%20If%20you%20don't%20have%20Microsoft%20365%20or%20Office%202021%2C%20confirm%20the%20formula%20with%20Ctrl%2BShift%2BEnter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3113769%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3113769%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1296610%22%20target%3D%22_blank%22%3E%40hstein1827%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20formula%20that%20I%20proposed%20should%20do%20that%2C%20but%20with%20different%20ranges%20of%20course.%3C%2FP%3E%0A%3CP%3EIn%20BY2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX(TableName%5BUPC_NO%5D%2C%20MATCH(1%2C%20(TableName%5BSTYLE%5D%3DD2)*('TableName%5BCLR_DESC%5D%3DE2)*('TableName%5BSIZE_NAME%5D%3DF2)%2C%200))%2C%20%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewhere%20TableName%20is%20the%20name%20of%20the%20table%20on%20the%20first%20sheet.%20If%20the%20data%20range%20is%20not%20a%20table%20yet%2C%20convert%20it%20to%20a%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

How can I run a formula that would span across multiple sheets and give me a specific cell while having multiple logical tests that it has to pass?

 

ex: if cells  Sheet 1 A1:A8 =  Sheet 2 B1, and Sheet 1 B1:B8 = Sheet 2 C1, and Sheet 1 C1:C8 = Sheet 2 D1, then the cell adjacent to the matching value is the answer?

 

Does that make any sense?

3 Replies

@hstein1827 

Do you want to return a value from column D on Sheet 1?

 

=IFERROR(INDEX('Sheet 1'!$D$1:$D$8, MATCH(1, (Sheet 1'!$A$1:$A$8=B1)*('Sheet 1'!$B$1:$B$8=C1)*('Sheet 1'!$C$1:$C$8=D1), 0)), "")

 

Change Sheet1 to the real name of that sheet. If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.

@Hans Vogelaar Essentially, if i want the highlighted value to populate from picture 1 to picture 2, what would you recommend?

 

It has to hit the logical tests of the style, clr_desc and size_name before it can give me a specific corresponding UPC_NO

 

Screen Shot 2022-02-03 at 4.48.42 PM.pngScreen Shot 2022-02-03 at 4.49.19 PM.png

@hstein1827 

The formula that I proposed should do that, but with different ranges of course.

In BY2:

 

=IFERROR(INDEX(TableName[UPC_NO], MATCH(1, (TableName[STYLE]=D2)*('TableName[CLR_DESC]=E2)*('TableName[SIZE_NAME]=F2), 0)), "")

 

where TableName is the name of the table on the first sheet. If the data range is not a table yet, convert it to a table.