Complex Formula Help

Copper Contributor

Complex Formula Help

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

Re: Complex Formula Help

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.

Re: Complex Formula Help

@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

Re: Complex Formula Help

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.