| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Compare 2 columns - new worksheets | Excel Worksheet Functions | |||
| compare two columns with different ranges in two worksheets | Excel Discussion (Misc queries) | |||
| how to compare columns in 4 worksheets | Excel Discussion (Misc queries) | |||
| using VLOOKUP to compare 2 columns to find new information | Excel Worksheet Functions | |||
| compare columns of different worksheets | Excel Discussion (Misc queries) | |||
Hybrid Mode
