If statement with Vlookup - including ISNA function to compare two columns from different worksheets

N

nakliwala

hi,

I need some help creatng an 'IF' statement that contains a
'VLOOKUP' . In Sheet 1 i have an empty column called ROC (Column F)
that should contain the 'IF/VLOOKUP' function. This should look up
Column B in Sheet 2 and try to match them with entries in Sheet
1:Column D(but only the first four digits of the entries in Sheet
2:Column B. In Sheet 2:Column B row 58 the entry is 5730P; in Sheet 1
Column D, Row 18 the entry is 5730. The 'VLOOKUP' function needs to
identify that the first 4 digits are similar, then output the value of
Sheet 2 Column 'A' row 58 in the Column F Row 18.



if anyone has knowledge of if statement with vlookups please get back
to me.

Thank you,
Nakli



To get more of an idea on my question; My question is similar to the
thread below:

Someone else's Question/Answer relating to vlookups::

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?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



CLR View profile
More options Aug 4 2006, 1:33 pm

Newsgroups: microsoft.public.excel.newusers
From: CLR <[email protected]>
Date: Fri, 4 Aug 2006 10:33:02 -0700
Local: Fri, Aug 4 2006 1:33 pm
Subject: RE: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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



- Hide quoted text -
- Show quoted text -

Sat3902 said:
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.



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.





Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 7 2006, 1:56 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Mon, 7 Aug 2006 13:56:46 -0400
Local: Mon, Aug 7 2006 1:56 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



CLR View profile
More options Aug 7 2006, 3:26 pm

Newsgroups: microsoft.public.excel.newusers
From: CLR <[email protected]>
Date: Mon, 7 Aug 2006 12:26:02 -0700
Local: Mon, Aug 7 2006 3:26 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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$14­63,3,FALSE))


hth
Vaya con Dios,
Chuck, CABGx3



- Hide quoted text -
- Show quoted text -

Sat3902 said:
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.



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.





Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 7 2006, 6:34 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Mon, 7 Aug 2006 18:34:31 -0400
Local: Mon, Aug 7 2006 6:34 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 7 2006, 6:41 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Mon, 7 Aug 2006 18:41:38 -0400
Local: Mon, Aug 7 2006 6:41 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Sat3902 Wrote:




- Hide quoted text -
- Show quoted text -
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

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



CLR View profile
More options Aug 8 2006, 8:46 am

Newsgroups: microsoft.public.excel.newusers
From: CLR <[email protected]>
Date: Tue, 8 Aug 2006 05:46:13 -0700
Local: Tues, Aug 8 2006 8:46 am
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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$14­63,3,FALSE))


Vaya con Dios,
Chuck, CABGx3



- Hide quoted text -
- Show quoted text -

Sat3902 said:
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.





Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 8 2006, 4:25 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Tue, 8 Aug 2006 16:25:14 -0400
Local: Tues, Aug 8 2006 4:25 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
:confused: 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. :confused:


--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 8 2006, 4:22 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Tue, 8 Aug 2006 16:22:54 -0400
Local: Tues, Aug 8 2006 4:22 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
:confused: 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. :confused:


--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 8 2006, 4:22 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Tue, 8 Aug 2006 16:22:54 -0400
Local: Tues, Aug 8 2006 4:22 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
:confused: 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. :confused:


--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 8 2006, 4:25 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Tue, 8 Aug 2006 16:25:14 -0400
Local: Tues, Aug 8 2006 4:25 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
:confused: 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. :confused:


--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



CLR View profile
More options Aug 8 2006, 8:03 pm

Newsgroups: microsoft.public.excel.newusers
From: "CLR" <[email protected]>
Date: Tue, 8 Aug 2006 20:03:34 -0400
Local: Tues, Aug 8 2006 8:03 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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


in
message


- Hide quoted text -
- Show quoted text -
:confused: 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. :confused:



http://www.excelforum.com/member.php?action=getinfo&userid=36777


- Hide quoted text -
- Show quoted text -




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 11 2006, 12:07 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Fri, 11 Aug 2006 12:07:30 -0400
Local: Fri, Aug 11 2006 12:07 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
:) 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),'She­et2'!$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 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?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



CLR View profile
More options Aug 11 2006, 12:54 pm

Newsgroups: microsoft.public.excel.newusers
From: CLR <[email protected]>
Date: Fri, 11 Aug 2006 09:54:02 -0700
Local: Fri, Aug 11 2006 12:54 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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,Shee­t2!$A$2:$B$3950,2,FALSE))))
 
P

Pete_UK

You won't be able to use VLOOKUP in this situation. When using
VLOOKUP, it searches for a match in the first (left-most) column of
the table and then returns data from columns on the right of the first
column. You say that you would like to search Sheet2!ColumnB and then
return the matching data from column A. To do this you will have to
use an INDEX/MATCH combination, or move the columns in your data table
so that your current column A becomes column C or later.

Hope this helps.

Pete
 
T

T. Valko

See reply at your other post

--
Biff
Microsoft Excel MVP


hi,

I need some help creatng an 'IF' statement that contains a
'VLOOKUP' . In Sheet 1 i have an empty column called ROC (Column F)
that should contain the 'IF/VLOOKUP' function. This should look up
Column B in Sheet 2 and try to match them with entries in Sheet
1:Column D(but only the first four digits of the entries in Sheet
2:Column B. In Sheet 2:Column B row 58 the entry is 5730P; in Sheet 1
Column D, Row 18 the entry is 5730. The 'VLOOKUP' function needs to
identify that the first 4 digits are similar, then output the value of
Sheet 2 Column 'A' row 58 in the Column F Row 18.



if anyone has knowledge of if statement with vlookups please get back
to me.

Thank you,
Nakli



To get more of an idea on my question; My question is similar to the
thread below:

Someone else's Question/Answer relating to vlookups::

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?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



CLR View profile
More options Aug 4 2006, 1:33 pm

Newsgroups: microsoft.public.excel.newusers
From: CLR <[email protected]>
Date: Fri, 4 Aug 2006 10:33:02 -0700
Local: Fri, Aug 4 2006 1:33 pm
Subject: RE: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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



- Hide quoted text -
- Show quoted text -

Sat3902 said:
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.



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.





Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 7 2006, 1:56 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Mon, 7 Aug 2006 13:56:46 -0400
Local: Mon, Aug 7 2006 1:56 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



CLR View profile
More options Aug 7 2006, 3:26 pm

Newsgroups: microsoft.public.excel.newusers
From: CLR <[email protected]>
Date: Mon, 7 Aug 2006 12:26:02 -0700
Local: Mon, Aug 7 2006 3:26 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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$14­63,3,FALSE))


hth
Vaya con Dios,
Chuck, CABGx3



- Hide quoted text -
- Show quoted text -

Sat3902 said:
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.



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.





Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 7 2006, 6:34 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Mon, 7 Aug 2006 18:34:31 -0400
Local: Mon, Aug 7 2006 6:34 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 7 2006, 6:41 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Mon, 7 Aug 2006 18:41:38 -0400
Local: Mon, Aug 7 2006 6:41 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Sat3902 Wrote:




- Hide quoted text -
- Show quoted text -
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

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



CLR View profile
More options Aug 8 2006, 8:46 am

Newsgroups: microsoft.public.excel.newusers
From: CLR <[email protected]>
Date: Tue, 8 Aug 2006 05:46:13 -0700
Local: Tues, Aug 8 2006 8:46 am
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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$14­63,3,FALSE))


Vaya con Dios,
Chuck, CABGx3



- Hide quoted text -
- Show quoted text -

Sat3902 said:
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.





Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 8 2006, 4:25 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Tue, 8 Aug 2006 16:25:14 -0400
Local: Tues, Aug 8 2006 4:25 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
:confused: 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. :confused:


--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 8 2006, 4:22 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Tue, 8 Aug 2006 16:22:54 -0400
Local: Tues, Aug 8 2006 4:22 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
:confused: 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. :confused:


--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 8 2006, 4:22 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Tue, 8 Aug 2006 16:22:54 -0400
Local: Tues, Aug 8 2006 4:22 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
:confused: 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. :confused:


--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 8 2006, 4:25 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Tue, 8 Aug 2006 16:25:14 -0400
Local: Tues, Aug 8 2006 4:25 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
:confused: 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. :confused:


--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



CLR View profile
More options Aug 8 2006, 8:03 pm

Newsgroups: microsoft.public.excel.newusers
From: "CLR" <[email protected]>
Date: Tue, 8 Aug 2006 20:03:34 -0400
Local: Tues, Aug 8 2006 8:03 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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


in
message


- Hide quoted text -
- Show quoted text -
:confused: 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. :confused:



http://www.excelforum.com/member.php?action=getinfo&userid=36777


- Hide quoted text -
- Show quoted text -




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



Sat3902 View profile
More options Aug 11 2006, 12:07 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 <[email protected]>
Date: Fri, 11 Aug 2006 12:07:30 -0400
Local: Fri, Aug 11 2006 12:07 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
:) 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),'She­et2'!$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 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?action=getinfo&userid=36777
View this thread: http://www.excelforum.com/showthread.php?threadid=568417




Reply Reply to author Forward Rate this post: Text
for clearing space







You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.



CLR View profile
More options Aug 11 2006, 12:54 pm

Newsgroups: microsoft.public.excel.newusers
From: CLR <[email protected]>
Date: Fri, 11 Aug 2006 09:54:02 -0700
Local: Fri, Aug 11 2006 12:54 pm
Subject: Re: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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,Shee­t2!$A$2:$B$3950,2,FALSE))))
 
N

nakliwala

You won't be able to use VLOOKUP in this situation. When using
VLOOKUP, it searches for a match in the first (left-most) column of
the table and then returns data from columns on the right of the first
column. You say that you would like to search Sheet2!ColumnB and then
return the matching data from column A. To do this you will have to
use an INDEX/MATCH combination, or move the columns in your data table
so that your current column A becomes column C or later.

Hope this helps.

Pete







- Show quoted text -

Hi Pete,
Thanks for your response,....

i used the following vlookup:

=IF(ISNA(VLOOKUP(D2,'Appendix 7'!$A$2:$B$103,2,FALSE))," ",
VLOOKUP(D2,'Appendix 7'!$A$2:$B$103,2,FALSE))

In the existing sheet the vlookup was in column F. And the value in
the "Appendix" tab column A was what needed to be matched. the
vlookup i used seemed to work fine...if u see any problems with it
please let me know.

Thanks for your help!

~Nakli
 
P

Pete_UK

This is looking for a match in column A of the Appendix sheet and will
return the corresponding value from column B of that sheet if a match
is found - this is not what you described in your first posting.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top