Complex INDEX MATCH question

[ehartmann55]

Hi folks,

Thanks for reading. This seems to be one of the most active excel forums on the web, and this is my first post, so thank you in advance for any help.

Situation, I am using an INDEX MATCH function to search a table of data for a specific NAME and return all ACCOUNTS connected to SALESPERSON.

The table I am pulling data from is built simply, exported from our sales app.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Jane Doe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account[/TD]
[TD]Salesperson[/TD]
[TD]Date[/TD]
[TD]Principle 1[/TD]
[TD]Notes 1[/TD]
[TD]Principle 2[/TD]
[TD]Notes 2[/TD]
[TD]Principle 3[/TD]
[TD]Notes 3[/TD]
[/TR]
[TR]
[TD]Burgers
[/TD]
[TD]Jane Doe[/TD]
[TD]1/1[/TD]
[TD]Buns[/TD]
[TD]abc[/TD]
[TD]Cheese[/TD]
[TD]def[/TD]
[TD]Sauce[/TD]
[TD]ghi[/TD]
[/TR]
[TR]
[TD]Pizza[/TD]
[TD]John Johns[/TD]
[TD]1/2[/TD]
[TD]Sauce[/TD]
[TD]abc[/TD]
[TD][/TD]
[TD]def[/TD]
[TD]Crust[/TD]
[TD]ghi[/TD]
[/TR]
[TR]
[TD]Pasta[/TD]
[TD]Fonz[/TD]
[TD]1/3[/TD]
[TD]Cheese[/TD]
[TD]abc[/TD]
[TD]Sauce[/TD]
[TD]def[/TD]
[TD][/TD]
[TD]ghi[/TD]
[/TR]
</tbody>[/TABLE]

B1 = Variable I am searching for to define the row of INDEX.
Current formula uses B1 value to display only matching results on a new tab and works just fine. Below...

=INDEX('Export Sheet'!A$2:A$50,SMALL(IF('Export Sheet'!$H$2:$L$50=$B$1,ROW('Export Sheet'!A$2:A$50)-ROW('Export Sheet'!A$2)+1),ROWS('Export Sheet'!A$2:'Export Sheet'!A2)))

My boss wants a more complex formula now and I am out of my depth.

Similarly, I need to search for a variable in B1, but it will be a Principle item. Before, the Names are all in 1 column. Now, principles are spread out into 3 columns, D, F, and H. I also need to display the Notes cell to the right of each matching Principle cell.

Resulting table should look like this if I searched for "Buns" using my example above.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Principle[/TD]
[TD]Cheese[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account[/TD]
[TD]Notes[/TD]
[TD]Salesperson[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]Burgers[/TD]
[TD]def[/TD]
[TD]Jane Doe[/TD]
[TD]1/1[/TD]
[/TR]
[TR]
[TD]Pasta[/TD]
[TD]abc[/TD]
[TD]Fonz[/TD]
[TD]1/3[/TD]
[/TR]
</tbody>[/TABLE]


[FONT=SegoeUI, Lato, Helvetica Neue, Helvetica, Arial, sans-serif]I have succeeded in creating a formula that populates the Accounts column properly with only matching principles. What I cannot find out how to do is to reference only the relevant notes cell, omitting the other 2, and give me the correct date.

I apologize for any wordiness or confusion, I am happy to clarify, share the file, or otherwise.

Thank you in advance!

Best,
Eddie[/FONT]

 

[ehartmann55]

Resulting table should look like this if I searched for "Buns" using my example above.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Principle[/TD]
[TD]Cheese[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account[/TD]
[TD]Notes[/TD]
[TD]Salesperson[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]Burgers[/TD]
[TD]def[/TD]
[TD]Jane Doe[/TD]
[TD]1/1[/TD]
[/TR]
[TR]
[TD]Pasta[/TD]
[TD]abc[/TD]
[TD]Fonz[/TD]
[TD]1/3[/TD]
[/TR]
</tbody>[/TABLE]

I neglected to clarify that all the data resides in a tab called "Export Sheet" and the data is being displayed in a new tab named after each principle. I use a drop down box with data validation to provide the options for B1, which automatically adjusts what is shown on the tab.