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

[nakliwala]

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!

 

[T. Valko]

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.

 

[nakliwala]

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











- 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

 

[T. Valko]

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 !

 

[David Biddulph]

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/community/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/guide/newsgroupfaq.mspx
--
David Biddulph

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