thanks, it didnt work at first but i understood the concept yo uwere using
and figured out that i just needed to add the location of the N:N column tab
name - then it worked - thanks - what a powerful formula!

"Rick Rothstein (MVP - VB)" wrote:

Here is the same formula, but deliberately broken apart so that your
newsreader won't split the text at the blank spaces...

=IF(ISNA(MATCH(TEXT(--LEFT(H6,5),"00")&"-"&--MID(H6,6,10)&
"-"&--RIGHT(H6,10),$B:$B,0)),"",INDEX(N:N,MATCH(TEXT(--LEFT(
H6,5),"00")&"-"&--MID(H6,6,10)&"-"&--RIGHT(H6,10),$B:$B,0)))

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I think I got all the ranges correct; give this formula a try...

=IF(ISNA(MATCH(TEXT(--LEFT(H6,5),"00")&"-"&--MID(H6,6,10)&"-"&--RIGHT(H6,10),'Drop
last comp report
here'!$H:$H,0)),"",INDEX(N:N,MATCH(TEXT(--LEFT(H6,5),"00")&"-"&--MID(H6,6,10)&"-"&--RIGHT(H6,10),'Drop
last comp report here'!$H:$H,0)))

Rick


"Belinda7237" wrote in message
...
I am trying to match data from two seperate spreadsheets to return a value
but the formatting of the data i need to match is not the same:

the id field in one sheet is:

0000100011634180000000265

and the data in the other set is:

01-1163418-265

these are actually the same and i would want to return the value in
column N.

I was trying to use this:
=IF(ISNA(MATCH(H6,'Drop last comp report here'!$H:$H,0)),"",INDEX('Drop
last
comp report here'!N:N,MATCH(H6,'Drop last comp report here'!H:$H,0),1))

but how do i acct for formatting difference?

Thanks