This is pretty messy, but you might give it a try.......it should return the
value in the first table if it's there, otherwise return from the second
table, if there....and if it's in neither, then return blank.........

=IF(AND(ISNA(VLOOKUP(A2,Sheet2!$A$2:$B$3950,2,FALS E)),ISNA(VLOOKUP(LEFT(A2,4),Sheet2!$A$2:$B$3950,2, FALSE))),"",IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$B$3950 ,2,FALSE)),VLOOKUP(LEFT(A2,4),Sheet2!$A$2:$B$3950, 2,FALSE),(VLOOKUP(A2,Sheet2!$A$2:$B$3950,2,FALSE)) ))

hth
Vaya con Dios,
Chuck, CABGx3


"Sat3902" wrote:


:) Happy days are here again.:)

Got it to work, with help from a friend and your assistance.

I just needed to add in the ( Left formula ). So if I do not get a
match on my 8 digit number then it will match on the first 4 digit
next.

Here is the formula.

=IF(ISNA(VLOOKUP(A2,'Sheet2'!$A$2:$B$3950,2,FALSE) ),VLOOKUP(LEFT(A2,4),'Sheet2'!$A$2:$B$3950,2,FALSE ),(VLOOKUP(A2,'Sheet2'!$A$2:$B$3950,2,FALSE)))

The only draw back about this is, I getting the #N/A value
when I don't get a hit. I can not seem to find the right mix to just
get a blank value if there is not match. :) I welcome your input. :)


Gracias por todo
Senor Chuck


--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417