Search values in a string and Return Values

[gmoorthy]

I have two spreadsheet

Spreadsheet A

Location City
3789 DALE
45678 AUSTIN


Spreadsheet B

Description Number
123 DALE SHORT XYTSYSYS
CITY OF AUSTIN AJAKSJSK


I want to look for CITY in spreadsheet A to match from Spreadsheet B and retrun the value from Number column from Spreadsheet B . ANy Excel formula which can do this ?

 

[SkipVought]

Hi,

Number column? Programmer, you've been around Tek-Tips since July 2004 and no TGML code for posting clear examples?

No formula to do a lookup for an embedded value. You'll need VBA, forum707. You've been there before.

Just a tip for a process that you could code... use the Find feature looking at the entire workbook with your macro recorder on. Post back in forum707 with your recorded code if you need help.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[gmoorthy]

Basically what i meant to say is that the second column in Spreadsheet B is "Number" and i wanted the value from that column. I am trying to use the Index , Match and COUNT if function to see if i can parse the value from description column in spreadsheet B and then use a vlookup to get the data

 

[SkipVought]

In your example, if you were to parse Description, 1) the parsed values (parsing on SPACE) step on the values in the Number column and 2) the values of interest end up in different columns. Tilt!

You need VBA!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[gmoorthy]

Used this to parse the value

=INDEX(Sheet2!$B$2:$B$3,MATCH(1,COUNTIF(C2,"*"&Sheet2!$B$2:$B$3&"*"),0))

 

[SkipVought]

First off your syntax is incorrect in COUNTIF(): =COUNTIF(range,Value).

Second the wildcard does not seem to work in COUNTIF().

As I suggested earlier, record a macro and post your recorded code. THIS WILL WORK, I guarantee!

BTW, I had to WORK at massaging your example. Could not use any native features to parse your data into proper columns. If you had properly posted you or examples using TGML tags, it would have been a breeze.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}