very difficult CSE (array formula) question

[Guest]

I have two files. file1 has all of my data (like a database) and file2 extracts and displays significant data from file1.

In the second file(file2) I am trying to write a CSE(Array Formula) which will print values into column A.

It should:
Look at all the values in file1!B2:B200
If the value in a row of the file1!B column is "high" then it should print the exact text of the corresponding row of the file1!E column into the next available slot in the A column of file2.

Example:
file1 contains the following data:

.	A	B	C	D	E
1	...	low	...	...	apples
1	...	high	...	...	oranges
1	...	low	...	...	pears
1	...	high	...	...	grapes

so . . . file2 should contain the following:

.	A	B	C	D	E
1	oranges	...	...	...	...
1	grapes	...	...	...	...

Thanks.

 

[Asala42]

I suppose you could use a variation of an Index Match (slightly different formula per column).

e.g: {=INDEX([file1]$A$2:$E$200,MATCH(A2&"high",[file1]$E$2:$E$200&[file1]$A$2:$A$200,0),3)}

..would be used to pull File1Column C data into file 2.

You may want to just suck it up and toss in a colunn to the left of you range in file A and write a quick concatenation formula (like =F2&B2 -assuming you just inserted a column to the left of the range) to get a combined criteria for a vlookup.

That would look like (non-CSE): =Vlookup(A2&"high",[File1]$A$2:$F$200,4,false) ->to pull File1Colunn D data for example.

Hope that helps
Adam S.