Using VLOOKUP to find a String Text

[iggyowner82]

Column A
[TABLE="width: 131"]
<TBODY>[TR]
[TD]
Quarterly Dividend</SPAN>[/TD]
[/TR]
[TR]
[TD]Bonus Dividend</SPAN>[/TD]
[/TR]
[TR]
[TD]Annual Dividend</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

Column B (list is longer but for example)

[TABLE="width: 556"]
<TBODY>[TR]
[TD]CLIFFS NATURAL RESOURCES - Quarterly Dividend 2011 - Ex Date</SPAN>[/TD]
[/TR]
[TR]
[TD]CONAGRA FOODS INC - Quarterly Dividend 2011 - Ex Date</SPAN>[/TD]
[/TR]
[TR]
[TD]PINNACLE WEST CAPITAL CORP - Quarterly Dividend 2011 - Ex Date</SPAN>[/TD]
[/TR]
[TR]
[TD]DIAMOND OFFSHORE DRILLING INC - Quarterly Dividend 2011 - Ex Date</SPAN>[/TD]
[/TR]
[TR]
[TD]DIAMOND OFFSHORE DRILLING INC - Bonus Dividend 2011 - Ex Date</SPAN>[/TD]
[/TR]
[TR]
[TD]MEADWESTVACO CORP - Quarterly Dividend 2011 - Ex Date</SPAN>[/TD]
[/TR]
[TR]
[TD]SEAGATE TECHNOLOGY PLC - Interim Dividend 2011 - Ex Date</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

Basically, I am trying to use a VLOOKUP to lookup column B against column A. If the word in column B has within the string "Quarterly Dividend" (As per the lookup in column A), I want the the lookup formula in columns C to say Quarterly Dividend. I am using the below formula but I cannot get the result I am looking for. Any tips would be appreciated: -

=VLOOKUP("*"&B2&"*",$A$2:$A$4,1,TRUE)

 

[Aladin Akyurek]

Column A
[TABLE="width: 131"]
<tbody>[TR]
[TD]Quarterly Dividend
[/TD]
[/TR]
[TR]
[TD]Bonus Dividend
[/TD]
[/TR]
[TR]
[TD]Annual Dividend
[/TD]
[/TR]
</tbody>[/TABLE]

Column B (list is longer but for example)

[TABLE="width: 556"]
<tbody>[TR]
[TD]CLIFFS NATURAL RESOURCES - Quarterly Dividend 2011 - Ex Date
[/TD]
[/TR]
[TR]
[TD]CONAGRA FOODS INC - Quarterly Dividend 2011 - Ex Date
[/TD]
[/TR]
[TR]
[TD]PINNACLE WEST CAPITAL CORP - Quarterly Dividend 2011 - Ex Date
[/TD]
[/TR]
[TR]
[TD]DIAMOND OFFSHORE DRILLING INC - Quarterly Dividend 2011 - Ex Date
[/TD]
[/TR]
[TR]
[TD]DIAMOND OFFSHORE DRILLING INC - Bonus Dividend 2011 - Ex Date
[/TD]
[/TR]
[TR]
[TD]MEADWESTVACO CORP - Quarterly Dividend 2011 - Ex Date
[/TD]
[/TR]
[TR]
[TD]SEAGATE TECHNOLOGY PLC - Interim Dividend 2011 - Ex Date
[/TD]
[/TR]
</tbody>[/TABLE]

Basically, I am trying to use a VLOOKUP to lookup column B against column A. If the word in column B has within the string "Quarterly Dividend" (As per the lookup in column A), I want the the lookup formula in columns C to say Quarterly Dividend. I am using the below formula but I cannot get the result I am looking for. Any tips would be appreciated: -

=VLOOKUP("*"&B2&"*",$A$2:$A$4,1,TRUE)

C2, copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$2:$A$4&" "," "&$B2&" "),$A$2:$A$4)

 

[Gary's Student]

Consider using MATCH() rather than VLOOKUP().
MATCH() supports wildcards.

 

[iggyowner82]

C2, copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$2:$A$4&" "," "&$B2&" "),$A$2:$A$4)

Wow!! That works....but not sure how it works lol. What does the "9.99999999999999E+307" do in the formula?

 

[Aladin Akyurek]

Wow!! That works....but not sure how it works lol. What does the "9.99999999999999E+307" do in the formula?

LOOKUP using that big number as look up value correlates the last numeric value coming from the FIND expression with the results range. Here is a step by step review:

1) Space chars are used in order to isolate an entry from its look-alikes: "iggyowner82" and "iggyowner824" are not the same. If not isolated/protected with spaces around, a SEARCH or FIND would see a match between "iggyowner82" and "iggyowner824" while either one of these functions would not report a match between " iggyowner82 " and " iggyowner824 ".

2) The formula works/evaluates like this:

=LOOKUP(9.99999999999999E+307,
  SEARCH(" "&$A$2:$A$4&" "," "&$B2&" "),$A$2:$A$4)

The SEARCH bit is looking for every item (string) in A2:A4 in B2, appropriately surrounded with space chars. This yields:

=LOOKUP(9.99999999999999E+307,{28;#VALUE!;#VALUE!},$A$2:$A$4)

LOOKUP correlates the position of the last numeric value in:

{28;#VALUE!;#VALUE!}

which is 1 with the value in A2:A4 at that position:

=LOOKUP(9.99999999999999E+307,
  {28;#VALUE!;#VALUE!},
  {"Quarterly Dividend";"Bonus Dividend";"Annual Dividend"})

The result is of course Quarterly Dividend. Note that LOOKUP ignores the error values if it can. The following links detail why such a big number as look up value and the workings:

http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
http://www.mrexcel.com/forum/excel-questions/310278-vlookup-multiple-matches-match-returned.html (post #7)