Formula For Providing A Cell Value That is Offset From A List Selection-CodePudding

I've been digging at this problem for a long time now and can't find a solution. I have a contact dropdown list that is dynamically generated based on the company that is selected. Below the contact I would like the person's email to be selected automatically (if you select "Brad" then it automatically enters [email protected] into the cell below).

I have all of the contact information on another sheet and each row essentially looks like this:

Company, address, contact #1, contact #2, contact #3, contact email #1, contact email #2, contact email #3.

So if you select contact #1, it should know to automatically put "contact email #1" in the cell below. It looks like I have to use offset along with index match, but I can't get any formulas to recognize anything other than the first contact in the list. It's like it has problems seeing each list item as a separate cell on the other sheet.

Sheet1 - Our Quote Sheet

Sheet 2 - Address Data

CodePudding user response：

This is going to be a relatively complex lookup-within-a-lookup as not only are you looking vertically down the sheet for the correct company, you're then looking horizontally for the correct contact.

To get the row number of the company, use =MATCH(A11, Sheet2!F:F).
Using that with INDIRECT function allows you to select the range of possible contacts;

=INDIRECT("Sheet2!I" & MATCH(A11, Sheet2!F:F) & ":K" & MATCH(A11, Sheet2!F:F))

Use that to pick 1/2/3 from your contacts range:

=MATCH(C9, INDIRECT("Sheet2!I" & MATCH(A11, Sheet2!F:F) & ":K" & MATCH(A11, Sheet2!F:F)))

Finally, use INDEX to return the corresponding email address (we'll reuse the INDIRECT section to point at the email addresses range too)

=INDEX(INDIRECT("Sheet2!L" & MATCH(A11, Sheet2!F:F) & ":N" & MATCH(A11, Sheet2!F:F)), 1, MATCH(C9, INDIRECT("Sheet2!I" & MATCH(A11, Sheet2!F:F) & ":K" & MATCH(A11, Sheet2!F:F))))

And Welcome to stack overflow!

CodePudding user response：

=INDEX(Sheet2!$L$3:$N$4,MATCH(A11,Sheet2!$F$3:$F$4,0),MATCH(C9,INDEX(Sheet2!$I$3:$K$4,MATCH(A11,Sheet2!$F$3:$F$4,0),0),0))

A11 = Company, C9 = Contact

Breakdown: MATCH(A11,Sheet2!$F$3:$F$4,0) gets us the row for company MATCH(C9,INDEX(Sheet2!$I$3:$K$4,MATCH(A11,Sheet2!$F$3:$F$4,0),0) gets us the column for contact within the appropriate row. We can then use found row and column as input for range contact emails.

Adjust $4 in all places to the end of your range.

Solution offered by Barnes uses INDIRECT, which is quite slow.