Previous Topic Next Topic

Excel formula

Closed
Chuffy - Sep 9, 2009 at 03:53 PM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Sep 12, 2009 at 12:51 AM
Hello everyone.

I am trying to create a formula which will compare the value of a portion of one cell, to a table array, and return the 2nd column of the table array if a match occurs.

Data to Compare

Sheet1!B2 has the following: A (brown) fox jumps high

Sheet2 has the table array, with the data to compare against in column A and the data value to return in column B

I currently use the following:

A2=VLOOKUP(B2,'Sheet2'!$A$1:$B$65535,2,FALSE)

I need to be able to compare only a portion of B2 with any portion of Sheet2!A1:B65535

I specifically want to compare anything between the parenthesis in B2 with anything in parenthesis in the array.

Thank you for any help.

Chris

1 reply

Answer 1 / 1
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Sep 12, 2009 at 12:51 AM
instead of B2 use the following idea
mid(B2, find "(" in B2 +1, find ")" in B2 -find "(" in B2 ))
0