What I am needing to do is search Sheet 1 for specific text and return value from column B. Here is an Example of what I have:

Sheet 1:

[TABLE="width: 500"]

CMTS

Node

[/td]1

ABC1

[/td]1

ID5

[/td]2

[/td]JOE7

[/td]2

[/td]MISS9

[/td]3

JACK3

[/td]3

[/td]SEE4

4

[/td]PRIDE8

[/TABLE]

Sheet 2

[TABLE="class: grid, width: 500"]

CMTS 1

[/td]CMTS 2

[/td]CMTS 3

[/td]CMTS 4

[/td]CMTS 5

[/td]ABC1

[/td]JOE7

[/td]JACK3

[/td]PRIDE8

[/td]ID5

[/td]MISS9

[/td]SEE4

[/td]

[/TABLE]

I have tried to use VLOOKUP, SEARCH, & ISNUMBER statements but cant get the results I need. Any help will be greatly appreciated.