Solved

Refining a XLS formula

Posted on 2014-02-11
9
135 Views
Last Modified: 2014-02-14
Yesterday I was given an excellent answer by Ken Butters who supplied me with a nice formula for my problem. But I need to refine it a bit. There was something I had not considered.  What I had not considered was the inconsistencies of the columns  like some name are in UPERCASSE some not and some with a combination of both. So I need for the below formula to reflect that situation.

Formula:
=IF(ISNA(VLOOKUP(A1363 Refining a XLS formula
,B:B,1,FALSE)),CONCATENATE(A1363," = No match in AMS"),VLOOKUP(A1363,B:B,1,FALSE))
0
Comment
Question by:d_asselin
  • 4
  • 4
9 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39850601
Vlookup is not case sensitive.  Are you saying you want to match case sensitivity too?
0
 

Author Comment

by:d_asselin
ID: 39850619
The problem is that I have some users that are not Identical in both columns.
Like below example and are not filtered properly.

    A           B                C
pedas      Pedas       Nomatch
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39850637
Which is the formula output in that example - what should the result be?

regards, barry
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:d_asselin
ID: 39850664
Microsoft Office Professional Plus 2010
Version 14.0.6123.5001
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39850680
Can you attach a workbook sample showing what you are trying to achieve?
0
 

Author Comment

by:d_asselin
ID: 39850717
This is what is happening.  The Pedas user & the AmsUser should still be in the found  
 
CI Name-CyberArk       AccountName-AMS      Acounts in CyberArk & AMS      
CGIMetaSync      a_svcbd_db_prd_ogt      CGIMetaSync      
AMSUSER       Archives-CentredeSer      AMSUSER      
AMSUSER       excbesadm      AMSUSER      
dbabatch                        a_svcbd_vw_cdvogtsql      dbabatch      
dbabatch        CGIBureaudeprojets      dbabatch      
oracle                        CGIEXCMSG01MOM08      oracle      
oraoms            demandesds                        ORAOMS      
orasat                        a_svcbd_is_cprogtsql      orasat      
oravip              archive                                          oravip      
perfstat                        CGIEXCMSG01MOM01      PERFSTAT      
rman                           centredeservices                   RMAN      
sys                          AmsUser                        AmsUser = No match in AMS       Should still be found in AMS
sys                          epo_agent_install                   sys = No match in AMS      
sysman                  a_svcbd_rs_prd_ogt                 sysman = No match in AMS      
Pedas                  pedas                                         Pedas = No match in AMS       Should still be found in AMS
0
 

Author Comment

by:d_asselin
ID: 39850723
Sorry about that post It did not look like this in the preview I will correct and repost
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39850752
It should be found with your formula.

Check if either Pedas in first column has a space at the end or if pedas in the second column has a space at the end.  That would be a reason for a "non match"
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39850764
Formula can also be shortened to:

=IFERROR(VLOOKUP(A1363,B:B,1,FALSE),A1363," = No match in AMS")
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question