A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using VLOOKUP (ISNA) function to compare 2 columns on different worksheets in excel



 
 
Thread Tools Display Modes
  #1  
Old September 20th, 2007, 10:27 PM posted to microsoft.public.excel.worksheet.functions
nakliwala
external usenet poster
 
Posts: 4
Default Using VLOOKUP (ISNA) function to compare 2 columns on different worksheets in excel

Hi,

I am currently trying to use a VLOOKUP function in one worksheet that
refers to a table on the another worksheet in the same Excel
workbook. I havent used VLOOKUP functions in awhile. The VLOOKUP
function in sheet A should look through the 'SGL' column in sheet B
and compare it to the values in sheet A under the column SGL


Sheet A:

SGL Account Fund Group Bureau cod Partner Code ROC
1310
'vlookup goes here'
1310
1410
2110
2980


3102
3103
3103


5200
5200
5720
5720
5730
5755








This is what the reference table data looks like(Sheet B):
Category SGL
1 1610R
1 2530P
1 1611R
1 2531P
1 1612R
1 2532P
1 1613R
1 2533P
1 1618R
1 1620R
1 2540P
1 1621R
1 1622R
1 1623R
1 1630R

The vlookup needs to
1) compare the two SGL columns after 'truncating the R or P) and if
any values from sheet B match sheet A after truncating the ending
letter (R or P) then it should bring over the category from sheet B
and show that value in Sheet A under ROC column. Also, if there are
gaps in the SGL column from sheet A(as you can see above in sheet A)
then the ISNA function should output a value of 0 in the Roc column
for that row. Any suggestions/help will be greatly appreciated.


Thank you!

  #2  
Old September 21st, 2007, 03:33 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Using VLOOKUP (ISNA) function to compare 2 columns on different worksheets in excel

Try this:

=IF(A2="","",IF(ISNA(MATCH(A2&"*",Sheet2!B$2:B$16, 0)),0,INDEX(Sheet2!A$2:A$16,MATCH(A2&"*",Sheet2!B$ 2:B$16,0))))

Copy down as needed.

--
Biff
Microsoft Excel MVP


"nakliwala" wrote in message
ups.com...
Hi,

I am currently trying to use a VLOOKUP function in one worksheet that
refers to a table on the another worksheet in the same Excel
workbook. I havent used VLOOKUP functions in awhile. The VLOOKUP
function in sheet A should look through the 'SGL' column in sheet B
and compare it to the values in sheet A under the column SGL


Sheet A:

SGL Account Fund Group Bureau cod Partner Code ROC
1310
'vlookup goes here'
1310
1410
2110
2980


3102
3103
3103


5200
5200
5720
5720
5730
5755








This is what the reference table data looks like(Sheet B):
Category SGL
1 1610R
1 2530P
1 1611R
1 2531P
1 1612R
1 2532P
1 1613R
1 2533P
1 1618R
1 1620R
1 2540P
1 1621R
1 1622R
1 1623R
1 1630R

The vlookup needs to
1) compare the two SGL columns after 'truncating the R or P) and if
any values from sheet B match sheet A after truncating the ending
letter (R or P) then it should bring over the category from sheet B
and show that value in Sheet A under ROC column. Also, if there are
gaps in the SGL column from sheet A(as you can see above in sheet A)
then the ISNA function should output a value of 0 in the Roc column
for that row. Any suggestions/help will be greatly appreciated.


Thank you!



  #3  
Old September 25th, 2007, 01:25 AM posted to microsoft.public.excel.worksheet.functions
nakliwala
external usenet poster
 
Posts: 4
Default Using VLOOKUP (ISNA) function to compare 2 columns on different worksheets in excel

On Sep 20, 10:33 pm, "T. Valko" wrote:
Try this:

=IF(A2="","",IF(ISNA(MATCH(A2&"*",Sheet2!B$2:B$16, 0)),0,INDEX(Sheet2!A$2:A$*16,MATCH(A2&"*",Sheet2!B $2:B$16,0))))

Copy down as needed.

--
Biff
Microsoft Excel MVP

"nakliwala" wrote in message

ups.com...



Hi,


I am currently trying to use a VLOOKUP function in one worksheet that
refers to a table on the another worksheet in the same Excel
workbook. I havent used VLOOKUP functions in awhile. The VLOOKUP
function in sheet A should look through the 'SGL' column in sheet B
and compare it to the values in sheet A under the column SGL


Sheet A:


SGL Account Fund Group Bureau cod Partner Code ROC
1310
'vlookup goes here'
1310
1410
2110
2980


3102
3103
3103


5200
5200
5720
5720
5730
5755


This is what the reference table data looks like(Sheet B):
Category SGL
1 1610R
1 2530P
1 1611R
1 2531P
1 1612R
1 2532P
1 1613R
1 2533P
1 1618R
1 1620R
1 2540P
1 1621R
1 1622R
1 1623R
1 1630R


The vlookup needs to
1) compare the two SGL columns after 'truncating the R or P) and if
any values from sheet B match sheet A after truncating the ending
letter (R or P) then it should bring over the category from sheet B
and show that value in Sheet A under ROC column. Also, if there are
gaps in the SGL column from sheet A(as you can see above in sheet A)
then the ISNA function should output a value of 0 in the Roc column
for that row. Any suggestions/help will be greatly appreciated.


Thank you!- Hide quoted text -


- Show quoted text -


Hi Biff,

Thanks for your efforts. I used the following vlookup function:

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

and it seems to be working right. I tried the function you supplied
and it did not work correctly, its hard to see what I was trying to
ask for since i didnt send u my excel file....thanks for your help
though...since its working we'll leave it at that for now...

out of curiousity do u work for google? or are you just a nice person
who answers ppls questions when they are stuck...either way your help
is greatly appreciated

-Nakli

  #4  
Old September 25th, 2007, 06:11 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Using VLOOKUP (ISNA) function to compare 2 columns on different worksheets in excel

"nakliwala" wrote in message
oups.com...
On Sep 20, 10:33 pm, "T. Valko" wrote:
Try this:

=IF(A2="","",IF(ISNA(MATCH(A2&"*",Sheet2!B$2:B$16, 0)),0,INDEX(Sheet2!A$2:A$*16,MATCH(A2&"*",Sheet2!B $2:B$16,0))))

Copy down as needed.



Hi Biff,

Thanks for your efforts. I used the following vlookup function:

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

and it seems to be working right. I tried the function you supplied
and it did not work correctly, its hard to see what I was trying to
ask for since i didnt send u my excel file....thanks for your help
though...since its working we'll leave it at that for now...

out of curiousity do u work for google? or are you just a nice person
who answers ppls questions when they are stuck...either way your help
is greatly appreciated

-Nakli



Thanks for the feedback!

I like to help out when I can. It's just a hobby !

--
Biff
Microsoft Excel MVP


  #5  
Old September 25th, 2007, 08:17 AM posted to microsoft.public.excel.worksheet.functions
David Biddulph
external usenet poster
 
Posts: 8,714
Default Using VLOOKUP (ISNA) function to compare 2 columns on different worksheets in excel

It's worth remembering that this group is *not* run by Google. It is a
usenet newsgroup, for which Google provide an archive and a web interface.

Similarly it is not run by Microsoft, although Microsoft do provide one of
the news servers on which the group is hosted.
[http://www.microsoft.com/technet/com.../nntpnews.mspx will tell you how
to connect to msnews.microsoft.com]

The answers on the group come from other Excel users, who are trying to help
others.
See also a FAQ at
http://www.microsoft.com/communities...sgroupfaq.mspx
--
David Biddulph

"nakliwala" wrote in message
oups.com...
....
out of curiousity do u work for google? or are you just a nice person
who answers ppls questions when they are stuck...either way your help
is greatly appreciated

-Nakli


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.