Excel Help Wanted - Specific Question?

Question

Column B has a list of last names. Column F has a seperate list of last names. Is there a function I can use to determine if a last name in column F appears in column B?

Answer 1

The exact works, but can be cumbersome.

The best method is vlookup. It is designed for exactly what you want to do. However, there are 2 things you need to do first.

1) get rid of unwanted spaces before or after the last name. To do this, use this formula in column C =trim(b1). This trims the spaces off and puts the remainder in column c. Repeat for the data in column F and put the result in Column G

Now copy the data in C and Paste Special if back into B. Repeat, moving data from G to F. Delete the data in columns C & G.

2) You have to sort the data in column B (your lookup column).

Now you have all that done, the vlookup.

in your now empty column F, do this:

=VLOOKUP(G1,$b$1:$b999,1,FALSE)

What this does is lookup what is in G1 and searches the range b1 through b999 (change this as needed), it will return the data in the first column (you can get back another column's data if you wish - you have to use multiple columns after the first ,) and it looks for an exact match (this is the "FALSE")

Repeat for each row.

Answer 2

EXACT

See Also

Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document.

Syntax

EXACT(text1,text2)

Text1 is the first text string.

Text2 is the second text string.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.


Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1
2
3
4
A B
First string Second string
word word
Word word
w ord word
Formula Description (Result)
=EXACT(A2,B2) Checks whether the strings in the first row match (TRUE)
=EXACT(A3,B3) Checks whether the strings in the second row match (FALSE)
=EXACT(A4,B4) Checks whether the strings in the third row match (FALSE)

Answer 3

what i understand by ur question is that if the value in the same row of Columns B and F Matches or Not for this : use =b1=f1 as function in one of the empty coloumns. if both matches it will return true else return false.
OR
Do u want to know that each of the values in Coloumn F has a Match in Coloumn B: for this justwrite this function in a blank coloumn: =vlookup(f1,$b$1:$b$200,1,false)
(considering there are 200 entries in Coloumn B.)
and drag this function to fill all the cells adjacent to Coloumn F.
all the Lastnames Matching will return the same value otherwise #NA will appear.

If this doesnot resolve ur problem, u can mail me ur file on kiran_j_jain @ yahoo.com and will resolve ur query
Regards,
Kiran Jain

Answer 4

on the previous answer, per chance it could be (an section) contained in the locate field and (a comma) contained in the replace field. Or, an progression, seek for first for 6 areas contained in the locate field, then repeat with in straight forward words 5 areas, and keep repeating until eventually you're all the way down to at least one area. That way, distinct the repeated areas would get replaced by in straight forward words one comma, as a replace of distinct commas. Oh, and also you may try the URL you gave, yet take off the "No" of the ending parameter "NoTables"; you may like that format extra efficient. desire it really is effective for you!

Answer 5

Try using an IF Function.

=IF($A$1=D6,TRUE,FALSE) - the dollar signs leave cell A1 the same so you can fill the reset of the cells with the formula without changing the cell references. the only reference that will change is D6 to D7, D8....when you fill the formula.

Answer 6

I just use the ctrl + f function to see if it finds the name twice.