Solved

XLS  Matching to columns

Posted on 2014-02-10
2
128 Views
Last Modified: 2014-02-10
Hi all

   I’m not an XLS person and I have a file with 2 columns of names. What I need to do is match names in column A to names in column b  and print in column C the names of column A that have a match in column B something like the example below

John      Paul       Paul
Paul      John      John
dan      Margret       dan
margret      Peter      Peter
peter      Robert       Dan = no match in B
0
Comment
Question by:d_asselin
2 Comments
 
LVL 19

Accepted Solution

by:
Ken Butters earned 500 total points
ID: 39848191
I've attached a spreadsheet that has a formula in Column C that will use Vlookup function to accomplish what you want.

Here is formula in C1... then did fill down to C5.
=IF(ISNA(VLOOKUP(A1,B:B,1,FALSE)),CONCATENATE(A1," = No match in B"),VLOOKUP(A1,B:B,1,FALSE))

what this does is take the value of John in A1 and checks to see if it can be found in column B.  If it can be found in Column B, it evaluates to the name in column A.

If it cannot be found... it takes the name in Column a and says it cannot be found in B.
Book1--1-.xlsx
0
 

Author Closing Comment

by:d_asselin
ID: 39848259
Perfect thank you

Dan
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now