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:

Column A	Column B
IFT	PSTN Fault Down 40mbps Up 10mbps
IFT	Fault found at Line. Please check Sync
AB	Fault found at Line. Please check sync
GG	Start faulting at PCP.
LK	Notify control office. Line 7 - 18/12/2014 13:42

<tbody>
</tbody>

On sheet2:

Column A	Column B
PSTN Fault	RED
Fault found at line.	AMBER
Start faulting at PCP	GREEN
Notify control office	RED

<tbody>
</tbody>

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!