IF & Vlookup

[Sat3902]

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

[CLR]

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
>
>

[Sat3902]

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.

[CLR]

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
>
>

[Sat3902]

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.

The database from where I am doing the lookup from does have the 8 digits and 4 digits in the same column. But the work sheet that I import the data needing to be matched up, I have 2 columns one for the 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 am not confusing you with what I am needing.

Gracias

[Sat3902]

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.

The database from where I am doing the lookup from does have the 8 digits and 4 digits in the same column. But the work sheet that I import the data needing to be matched up, I have 2 columns one for the 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 am not confusing you with what I am needing.
Gracias

Have a good day

[CLR]

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
>
>

[Sat3902]

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]

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 on the 8 digit but did not do the vlookup 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 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.

[CLR]

I'm ready for bed now and cannot do any more tonight........all I can see
off the bat is that you do not have the "Sheet2! reference on the middle
part of the formula.........you might try this.........
=IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE)),"invalid number,
"&VLOOKUP(LEFT(A16,4),Sheet2!$A$2:$C$20882,3,FALSE),VLOOKUP(A16,Sheet2!$A$2:
$B$20882,2,FALSE))

Otherwise, maybe you might send me a copy of your file to .......croberts
at tampabay dot rr dot com.......and I'll take a look tomorrow........sorry,
I'm out of gas tonight, but hang in there....we'll whip this thing.

Vaya con Dios,
Chuck, CABGx3





"Sat3902" <[email protected]> wrote in
message news:[email protected]...
>
> 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 on the 8 digit but did not do the vlookup
> 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 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
>

[CLR]

I'm ready for bed now and cannot do any more tonight........all I can see
off the bat is that you do not have the "Sheet2! reference on the middle
part of the formula.........you might try this.........
=IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE)),"invalid number,
"&VLOOKUP(LEFT(A16,4),Sheet2!$A$2:$C$20882,3,FALSE),VLOOKUP(A16,Sheet2!$A$2:
$B$20882,2,FALSE))

Otherwise, maybe you might send me a copy of your file to .......croberts
at tampabay dot rr dot com.......and I'll take a look tomorrow........sorry,
I'm out of gas tonight, but hang in there....we'll whip this thing.

Vaya con Dios,
Chuck, CABGx3





"Sat3902" <[email protected]> wrote in
message news:[email protected]...
>
> 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 on the 8 digit but did not do the vlookup
> 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 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
>

[Sat3902]

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

[CLR]

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,FALSE)),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
>
>