INDEX Confusion: Return related data from multiple columns

[Eddie Hartmann]

Greetings community, 

I am having an issue with a exported file I am trying to manipulate into returning specific data based on the location of the matching reference. 

Situation...my company exports a monthly sales table which contains, in each row, account name, sales person, product 1, notes 1, product 2, notes 2, product 3, notes 3, etc, etc. going horizontally.

Initially, I was successful in creating a formula that can return rows of data that match a specific Sales Person because the array I was searching is only 1 column and I wanted the entire matching row. 

Formula I use: 

=IFERROR(IF(INDEX('Export Sheet'!A$2:A$153,SMALL(IF('Export Sheet'!$E$2:$E$153=$B$1,ROW('Export Sheet'!A$2:A$153)-ROW('Export Sheet'!A$2)+1),ROWS('Export Sheet'!A$2:'Export Sheet'!A2)))=0, "NO DATA", INDEX('Export Sheet'!A$2:A$153,SMALL(IF('Export Sheet'!$E$2:$E$153=$B$1,ROW('Export Sheet'!A$2:A$153)-ROW('Export Sheet'!A$2)+1),ROWS('Export Sheet'!A$2:'Export Sheet'!A2)))), "")

Where B1 = Sales Person I am searching for.

The formula runs twice to return "NO DATA" if there is nothing present

New problem: the boss wants me to return Account Names and Notes based on which Product that sales person sold. The issue is the products are in three columns, and I only want to return the notes cell that is directly to the right of the product. 

As of now, the formula is locating the right account names based on the product, but I cannot figure out how to return only the Notes column I want, not all 3 of them. 

My solution I cannot craft right is to capture the column number of the 1st match if finds, like small does, but all I can get is the small number. 

I can clarify or share the work book if it would help anyone solve the problem. 

Thank you for any assistance given!

[#2]

Hi Eddie,

You may be able to address the issue by using Power Query to "Unpivot" the monthly sales Table and then do your reporting / analysis via a Pivot Table?

If you can share your file or a sanitised version then that would help

Thanks 

[#3]

Eddie - Can we use something like text-to-columns to seperate the single column of exported data into individual data fields and then perform a lookup? The ability to transform data using Power Query might be an even better approach because we can reuse the transformation each time we get a new export file.