find row that contains text

[grezmo]

Hi,

I'm struggling with the final part to a spreadsheet. Can't work out how to upload the file so hopefully this will make sense.

On sheet1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD] Column B[/TD]
[/TR]
[TR]
[TD]IFT[/TD]
[TD]PSTN Fault Down 40mbps Up 10mbps[/TD]
[/TR]
[TR]
[TD]IFT[/TD]
[TD]Fault found at Line. Please check Sync[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]Fault found at Line. Please check sync[/TD]
[/TR]
[TR]
[TD]GG[/TD]
[TD]Start faulting at PCP.[/TD]
[/TR]
[TR]
[TD]LK[/TD]
[TD]Notify control office. Line 7 - 18/12/2014 13:42[/TD]
[/TR]
</tbody>[/TABLE]

On sheet2:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]PSTN Fault[/TD]
[TD]RED[/TD]
[/TR]
[TR]
[TD]Fault found at line.[/TD]
[TD]AMBER[/TD]
[/TR]
[TR]
[TD]Start faulting at PCP[/TD]
[TD]GREEN[/TD]
[/TR]
[TR]
[TD]Notify control office [/TD]
[TD]RED[/TD]
[/TR]
</tbody>[/TABLE]

So my dilemma lies in:

I'm trying to get Sheet1!B2 to be found in Sheet2!A:A but the text needs to be a contains. As the text length changes from fault to fault I can't trim it. I want it to give me the row number that it finds the text in.

I've tried index, match, vlookup, find etc. but have hit a wall as the text doesn't match perfectly.

Thanks for your help in advance

Graham

 

[Crystalyzer]

If you can sort the data on Sheet 2 in Ascending order you can use the MATCH function with the match type = 1.

=MATCH(B1,Sheet2!$A$1:$A$4,1)

 

[grezmo]

Thanks Crystalizer. If I rank them A-Z it gives me the row number. Awesome

 

[Crystalyzer]

Thanks Crystalizer. If I rank them A-Z it gives me the row number. Awesome

Anytime!