get 4 words before a match found included match word

[satish78]

In ColumnA I have sentences wrapped and in columnB list of words

formula should lookup range of words in columnA and get 4 words including matched word.
for example.

Robert is honored in computer science and Robert earned GPA 3.6 and he has a bachelors degree (ColumnA)

3.5 (ColumnB Range)
3.6
3.7
4.9

Robert earned GPA 3.6 (ColumnC result)

 

[Aladin Akyurek]

Your sample does not match your verbal description like "get 4 words including matched word".

 

[satish78]

here is sample file

https://drive.google.com/file/d/13Rna_KVaar_FFTIe2D9c15EXXqA9x0NK/view?usp=sharing

Your sample does not match your verbal description like "get 4 words including matched word".

 

[Aladin Akyurek]

here is sample file

https://drive.google.com/file/d/13Rna_KVaar_FFTIe2D9c15EXXqA9x0NK/view?usp=sharing

This is what you are linking us to, instead of posting it as data directly here:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td]Robert is honored in computer science and Robert earned GPA 3.6 and he has a bachelors degree[/td][td]

3.5​

[/td][td]Robert earned GPA 3.6[/td][td][/td][/tr]
[tr][td]

2​

[/td][td][/td][td]

3.6​

[/td][td][/td][td][/td][/tr]
[tr][td]

3​

[/td][td][/td][td]

3.7​

[/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td][/td][td]

4.9​

[/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Note that you don't mention what the conditions are to output the result that you show...

 

[satish78]

i would like to get data where "3.6" is matched in Row1 the formula should get "robert earned gpa" including "3.6" data in ColumnC

This is what you are linking us to, instead of posting it as data directly here:

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Robert is honored in computer science and Robert earned GPA 3.6 and he has a bachelors degree[/TD]
[TD]

3.5​

[/TD]
[TD]Robert earned GPA 3.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

3.6​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

3.7​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

4.9​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Note that you don't mention what the conditions are to output the result that you show...

 

[Aladin Akyurek]

Still unclear. What do we look for, where do we look at, and what output must be returned?

 

[satish78]

Hi Aladin,

I want to find "3.6" in columnA, if found, the formula should get "Robert earned GPA" from ColumnA along with "3.6" in preceding ColumnC.(row1)

Hope you understand now.