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]

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]

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

 

[Sat3902]

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

 

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

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

 

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

 

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

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
match on my 8 digit number then it will match on the first 4 digi
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 :confused
when I don't get a hit. I can not seem to find the right mix to jus
get a blank value if there is not match. I welcome your input. :


Gracias por todo
Senor Chuc

 

[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