Index Match Array formula partially working Google sheets

[AlexaS]

Hello! I've got a question about an array index match formula that's partially working.

So the set up of the data is on sheet2 I have the raw data being downloaded. On sheet1 I have a table with the formulas pulling back only the relevant information from the data dump. The table set up is basically the top row is what I'm searching for in the formula. The data has more products, but I only care about the ones I'm searching for. The term that I'm matching on is in column B.

Here's an approximate set up:

A C Q W F
Person A - Station 1
- Station 2
- Station 3

Person B - Station 4
- Station 5

The formula is: =ArrayFormula(if($B2="","",if(ISTEXT(index('raw data'!$B$2:$Z$499,match($B2,'raw data'!$B$2:$B$499,0),match(C$1,'raw data'!$G2:$Z2,0))),"Yes","No")))

B2 is the station#, and C1 is the first product I'm looking for. When I run this formula it appears to work. HOWEVER, we noticed that the first line (so Person A - Station 1) only returns the last product found (so if A C and F were in the raw data, I only get a "yes" on F). But the station 2 line seems to return all the products found in the raw data. I cannot figure out why this would be partially working. Any help would be greatly appreciated.

Thank you!

 

[Fluff]

Is this for Excel or Google Sheets?

 

[AlexaS]

Is this for Excel or Google Sheets?

It's for Google Sheets. I'm so used to working with Excel I didn't even think about that.

 

[Fluff]

Ok, in future please make it clear that you are using Google Sheets & post your questions in the General Discussion & Other Applications section of he board.

 

[Fluff]

It would help if you could post some data of the raw data tab, or post a link to your file making sure it's shared

 

[AlexaS]

It would help if you could post some data of the raw data tab, or post a link to your file making sure it's shared

Per company rules, I can't post the file.

The raw data file is basically: person - station# - product: ABCDEFGHIJKLMNOP (but in any order)

The way I thought the formula worked was that it found the station# in the raw data matching my table (B2), returned the line number (2-500), and then searched the line for the product that matches the value in C1. For the 10 products I'm looking for, the formula updates C1, D1, E1, etc, and also updates the line number to search going down. We are manually fixing the line number to search (second match value G2:Z2) to start where another formula tells us the persons information starts in the raw data.

 

[Fluff]

The formula you posted doesn't make much sense & without any idea of how your data is laid out, I cannot really help.