Complex Formula Help

Copper 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


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


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.