Reference multiple cells in multiple columns and return "text1" or "blank" if conditions met

Question

In this example, the goal is to have three separate drop-down fields in Field1, Field2, and Field3. Each of these fields will reference data contained in the lower right-hand corner of the spreadsheet, ie: abc, def, geh, or fij in Field1. The goal is to have Field4 (D1) reference the data the user selects from the drop-down fields A2, B2, and C2, and return referenced text from Field4 (L18-L22) in the D2 cell.

In this scenario, I've achieved this result using an IF formula, but I was wondering if there is a simpler syntax available to achieve this result.

Did either of the answers below work for you? If so please mark one as correct. It is something only the person posing the question can do. Click on the green check mark by the one that is correct, the one you used. — Scott Craner
– Scott Craner, Commented Jan 28, 2016 at 15:15

Scott Craner · Accepted Answer · 2016-01-15 16:03:49Z

1

You could use this array formula:

=IFERROR(INDEX($L$19:$L$22,MATCH(1,IF(($I$19:$I$22 = A2)*($J$19:$J$22 = B2)*($K$19:$K$22 = C2),1,0),0)),"")

It is an array formula and must be confirmed with Ctrl-Shift-Enter. Put in D2 and confirm with Ctrl-Shift-Enter. Then Copy down the desired rows.

edited Jan 15, 2016 at 16:03

answered Jan 15, 2016 at 15:40

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user4039065 · Accepted Answer · 2016-01-15 15:47:56Z

1

Use the AGGREGATE function for a quick three column match.

In D2 as,

=IFERROR(INDEX(L$19:L$22, AGGREGATE(15, 6, ROW($1:$4)/((I$19:I$22=A2)*(J$19:J$22=B2)*(K$19:K$22=C2)), 1)), "")

Fill down as necessary. The IFERROR function takes care of non-matches.

answered Jan 15, 2016 at 15:47

user4039065

Collectives™ on Stack Overflow

Reference multiple cells in multiple columns and return "text1" or "blank" if conditions met

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related