Excel function

[jjst34]

I want to use some type of a lookup function,, but not quite sure how to write it... This is what I want it to do. I have 2 spreadsheets.
1st spreadsheet

Column A (No particular row, will need to search to find)
0101- - - -A010- - -10-1-01-1005-001-99

Column B (No particular row, will need to search to find)
Bank of New Hampshire

Column C (No particular row, will need to search to find)
8,513.39



2nd spreadsheet

Column A1
0101-1005-001

Column B1
I would like this cell to have the function in it to seach for the info in A1 inside the first spreadsheet and return the value in column C (8,513.39). As you will notice by the red values, the data in spreadsheet 2 cell A1 is inside of the large string of #'s in spreadsheet 2 Column A.. So I'd need for the function to search... Get what I'm saying.. This would save me so much time in what I'm trying to do, but I don't know how to make it search. I tried Vlookup but it always returns a 0. Thanks in advance for the help!!!

[Epidemic]

Are these fields always in the same format?

I mean characters 1-4 being the indicated red?

as well as characters 24 - 32 indicating the second part of your desired number?

[jjst34]

yes they are always in this format. If it helps.. the range of the data from the first spreadsheet goes from A24:C1878,,, so basically 1800 rows of the same format, just different #'s & dollar amounts... this would be the area I'd need to search throughout obviously... Thanks a bunch!

[Epidemic]

If that is the case what I would try would be outside of a function.

I would use Vlookup or some such, But I would use some text functions such as Mid, and left and right.

I would take that first field and for ease I would parse out the information to look like what you have on sheet 2.

Something to the effect of this column d =left(a1,4)&"-"&Mid(a1,24,8)

This is off the top of my head so you may need to work with me here.

The result of this will be 0101-1005-001 which will match the information from sheet 2's vlookup.

of course use the new value in column D

Note I cant remember what the problem with vlookup is but it is best to make sure that they are sorted by column D in this case because vlookup likes things in order sometimes.

[Epidemic]

http://home.mchsi.com/~lvahle1470/pa...up_example.xls

This may be close to what you want.

It is in Office XP so if you cant load it I can try again.

[Epidemic]

that is a simplified version of what you want.

If it does what you want please let me know.

[jjst34]

this is going to work wonderfully! I never used that "left" function before... I was trying to do it w/ MID & AND & Vlookup... I couldn't get it this perfect though.. Thanks a lot! Do you go to certain sites to learn these? I've taken excel courses before, but they don't get into the functions too much... have any recommendations... Thanks again!!!

[Epidemic]

I can be found here frequently and we have a little excel forum.
http://pierpaolo.proboards18.com/index.cgi

But the place which has been gold to me is
http://www.ozgrid.com/forum/

Excel can do anything you can imagine up with enough work or programming.

[Epidemic]

As for where I found my training. I am self taught. I have stumbled around the books on occasion but most of it is learned by years of problem solving.

I can program up a storm in Excel and have written 10's of thousands of lines of code through the years for excel macro's

[jjst34]

thanks.. you will see me there then!! I am in the process of reading an excel vba programming book for dummies. anyways,, w/ a couple adjustments to your functions, I got exactly what I needed.. thanks again!!!