Afternoon,

I know some one out there might be able to assist me with my dilemna.

In Sheet1 I have a list of 8 digts id numbers in column A and in sheet2
is where I am doing the lookup from.

The formula below is working great. I picked up from this website.


=IF(ISNA(VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE)),"Invalid
Number",VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE))

What I need to know if possible is, can another (vlookup or if ) be
added to the ending formula to do search on just the first 4 number of
the id, if I got the response Invalid Number.

I currently have another column doing the lookup on just the 4 digits.


Thanks


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

You might try something like this.........

=IF(ISNA(VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE)),"Invalid
Number"&", Four-digit lookup =
"&YourFourDigitLookpuFormula,VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE))

Vaya con Dios,
Chuck, CABGx3


"Sat3902" wrote:

>
> Afternoon,
>
> I know some one out there might be able to assist me with my dilemna.
>
> In Sheet1 I have a list of 8 digts id numbers in column A and in sheet2
> is where I am doing the lookup from.
>
> The formula below is working great. I picked up from this website.
>
>
> =IF(ISNA(VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE)),"Invalid
> Number",VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE))
>
> What I need to know if possible is, can another (vlookup or if ) be
> added to the ending formula to do search on just the first 4 number of
> the id, if I got the response Invalid Number.
>
> I currently have another column doing the lookup on just the 4 digits.
>
>
> Thanks
>
>
> --
> Sat3902
> ------------------------------------------------------------------------
> Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
> View this thread: http://www.excelforum.com/showthread...hreadid=568417
>
>

Gracias Chuck,

Thank you for the suggestion, however it did not work for me. I still
got a return of "*Invalid Number*". I do have the 4 digit in the Sheet1
where I am doing the lookup from.

I am hoping I followed example

Here is what I entered.

=IF(ISNA(VLOOKUP(A7,Schedule!$A$13:$E$1463,3,FALSE)),"Invalid
Number"&"",(VLOOKUP(LEFT(A7,4),Schedule!$A$13:$E$1463,3,FALSE)))

The last part of the arguement does work correctly. I currently using
it when doing a vlookup on just the 4 digit on a seperate column.

I am doing a vlookup on my 8 digit user ID and when I do not get a
match I then what do a vlookup on the first 4 digits only.


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

If you are wanting to look up the LEFT 4 digits of a cell, then you will have
to build that in to your VLOOKUP table, or another one.....unless you have
both cells containing just those 4 digits AND other cells containg the entire
number.....VLOOKUP cannot extract the left 4 digits out af a number in the
table.....just add a column on the left side of the table....assume you
insert a new column A and the old column A is now B and the table extends to
F now, then try

=IF(ISNA(VLOOKUP(A6,Schedule!$B$13:$F$1463,3,FALSE)),"Invalid
Number"&",
"&vlookup(left(A6,4),$A$13:$F$1463,3,false),VLOOKUP(A6,Schedule!$B$13:$F$1463,3,FALSE))

hth
Vaya con Dios,
Chuck, CABGx3



"Sat3902" wrote:

>
> Gracias Chuck,
>
> Thank you for the suggestion, however it did not work for me. I still
> got a return of "*Invalid Number*". I do have the 4 digit in the Sheet1
> where I am doing the lookup from.
>
> I am hoping I followed example
>
> Here is what I entered.
>
> =IF(ISNA(VLOOKUP(A7,Schedule!$A$13:$E$1463,3,FALSE)),"Invalid
> Number"&"",(VLOOKUP(LEFT(A7,4),Schedule!$A$13:$E$1463,3,FALSE)))
>
> The last part of the arguement does work correctly. I currently using
> it when doing a vlookup on just the 4 digit on a seperate column.
>
> I am doing a vlookup on my 8 digit user ID and when I do not get a
> match I then what do a vlookup on the first 4 digits only.
>
>
> --
> Sat3902
> ------------------------------------------------------------------------
> Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
> View this thread: http://www.excelforum.com/showthread...hreadid=568417
>
>

Evening Chuck

I am going to try your suggestion. Just want to mention to you tha
the VLoop can strip the LEFT 4 digits. I am currently using thi
arguement

=IF(ISNA(VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439,3,FALSE)),"Invali
Number",VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439,3,FALSE)) on the sam
work sheet. I am just trying to eliminate from having a lot of column
with formulas which slow up the workbook when saving updates or makin
change to it.

I do have the 8 digits and 4 digits in the same column but when doin
the vlookup I have 2 columns one for the 8 digits and the other for th
4 digit.

The end results is being populated to another worksheet.

Gracia

--
Sat390
-----------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...fo&userid=3677
View this thread: http://www.excelforum.com/showthread.php?threadid=56841

Sat3902 Wrote:
> Evening Chuck
>
> I am going to try your suggestion. Just want to mention to you tha
> the VLoop can strip the LEFT 4 digits. I am currently using thi
> arguement
>
> =IF(ISNA(VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439,3,FALSE)),"Invali
> Number",VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439,3,FALSE)) on the sam
> work sheet. I am just trying to eliminate from having a lot of column
> with formulas which slow up the workbook when saving updates or makin
> change to it.
>
> The database from where I am doing the lookup from does have the
> digits and 4 digits in the same column. But the work sheet that
> import the data needing to be matched up, I have 2 columns one for th
> 8 digits and the other for the 4 digit to do the Vlookup.
>
> The end results is being populated to another worksheet. I hope I a
> not confusing you with what I am needing.
> Gracias

Have a good da

--
Sat390
-----------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...fo&userid=3677
View this thread: http://www.excelforum.com/showthread.php?threadid=56841

Well then, if you have both the 4 digit and 8 digit numbers in column A then
this will probably work.....

=IF(ISNA(VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE)),"Invalid
Number,
"&vlookup(left(A6,4),$A$12:$E$1439,3,false),VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE))

Vaya con Dios,
Chuck, CABGx3



"Sat3902" wrote:

>
> Evening Chuck
>
> I am going to try your suggestion. Just want to mention to you that
> the VLoop can strip the LEFT 4 digits. I am currently using this
> arguement
>
> =IF(ISNA(VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439,3,FALSE)),"Invalid
> Number",VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439,3,FALSE)) on the same
> work sheet. I am just trying to eliminate from having a lot of columns
> with formulas which slow up the workbook when saving updates or making
> change to it.
>
> I do have the 8 digits and 4 digits in the same column but when doing
> the vlookup I have 2 columns one for the 8 digits and the other for the
> 4 digit.
>
> The end results is being populated to another worksheet.
>
> Gracias
>
>
> --
> Sat3902
> ------------------------------------------------------------------------
> Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
> View this thread: http://www.excelforum.com/showthread...hreadid=568417
>
>

Chuck

Thank you for your time and patients. I tried your suggestion from thi
morning. It only work the first part of the formula.

Here is what I entered
=IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE)),"invali
number,"&VLOOKUP(LEFT(A16,4),$A$2:$C$20882,2,FALSE),VLOOK
P(A16,Sheet2!$A$2:$B$20882,2,FALSE))

Got a return value of #N/A when I did not get a match on the 8 digit
It worked when I got a match.

Was I suppose to space the last letter of Vlookup at the last Vlooku
argument. I followed your example. It did the same without the same.

Again Thank your assistance Please don't stop.
Unless it can not be done. :confused

--
Sat390
-----------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...fo&userid=3677
View this thread: http://www.excelforum.com/showthread.php?threadid=56841

Chuck

Thank you for your time and patients. I tried your suggestion from this
morning. It only work the first part of the formula.

Here is what I entered
=IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE)),"invalid
number,"&VLOOKUP(LEFT(A16,4),$A$2:$C$20882,2,FALSE),VLOOKU
P(A16,Sheet2!$A$2:$B$20882,2,FALSE))

Got a return value of #N/A when I did not get a match on the 8 digit.
It worked when I got a match.

Was I suppose to space the last letter of Vlookup at the last Vlookup
argument. I followed your example. It did the same without the same.

Again Thank your assistance Please don't stop.
Unless it can not be done.


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

Chuck

Thank you for your time and patients. I tried your suggestion from thi
morning. It only work the first part of the formula.

Here is what I entered
=IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE)),"invali
number,"&VLOOKUP(LEFT(A16,4),$A$2:$C$20882,2,FALSE),VLOOK
P(A16,Sheet2!$A$2:$B$20882,2,FALSE))

Got a return value of #N/A when I did not get a match on the 8 digit.

It worked when I got a match on the 8 digit but did not do the vlooku
on the 4 digit that is when I got the *#N/A*

Was I suppose to space the last letter of Vlookup at the last Vlooku
argument. I followed your example. It did the same without the same.

Again Thank your assistance Please don't stop.
Unless it can not be done. :confused

--
Sat390
-----------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...fo&userid=3677
View this thread: http://www.excelforum.com/showthread.php?threadid=56841