Compare two columns and return text string

cathy - Jan 21, 2010 at 10:27 AM
 cathy - Jan 21, 2010 at 11:13 AM

Does anyone know how to write a formula in excel which compares column A to column C, sees if the numbers in these two columns are the same and then (if the numbers are the same) returns in column D the text string from column B when the number in column C matches the number in column A? I've tried to make VLOOKUP work, but I can't figure it out.

Thank's for all help! (If I'm being unclear, please let me know. English is not my first language)

Here is an example

Col A Col B Col C Col D
Row 1 0 Nurse 3 Text string to be returned: Engineer
Row 2 1 Social worker 0 Text string to be returned: Nurse
Row 3 2 Police 1
Row 4 3 Engineer 5
Row 5 4 Scientist 2
Row 6 5 Doctor 6
Row 7 6 Journalist 4

1 response

I'm replying to myself, which is pretty weird, but I found a solution at this site:

Thought it would be nice for others with similar problems to know that there is a solution out there; at least it worked for me:)

Below I'm repeating the question and the answer on the web site I mention over, just in case anyone has a problem loading the web site:

/***Title: Excel/merge two columns (match column's text string) ***/

Expert: Nathan Head - 4/18/2007

I would like to create a new column to describe LIST A. How to match the text string in LIST B with LIST A, and use the corresponding description from LIST B for LIST A?

LIST A LIST B description
111 111 111-000-111
111 222 222-000-222
333 333 333-000-333
333 444 444-000-444
333 555 555-000-555
333 666 666-000-666
444 777 777-000-777
555 888 888-000-888
666 999 999-000-999


Presuming I understand the question correctly, this can be done with VLOOKUP.

1) List A {111,111,333,333,333,333,444, etc} is located in rows 2 through 17 of column A in Excel
2) List B {111,222,333,444, etc} is located in rows 2 through 10 of column D in Excel
3) The descriptions of list B are located in column E, also rows 2 through 10, immediately to the right of their respective numbers.

We now want the descriptions to appear immediately to the right of the numbers in column A.

Insert this formula into cell B2:

It essentially does this:
Take the value in A2, 111, and find it in column D. When it finds a match in column D, go "2" columns over (D=1, E=2) and return the value shown there. FALSE = Find an exact match.

You can simply copy the above formula down through Row 17 of column B to fill in the remainder of the descriptions.

Let me know if you need an example of this and I will email you a copy.