Comparing Columns and returning value

Closed
Werdna - Apr 14, 2010 at 09:34 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Apr 15, 2010 at 03:06 PM


Hi,

Hopefully somebody can help me here!

Basically, I have 4 columns.

Column A has a list of names
Column B is empty and will need to be populated
Column C also has a list of names, some are identical to those in column A
Column D has values applicable to the names in column B

I would like to compare columns A and C, if a name in column A can be found anywhere in column C I would like to take the value from column C for the specific name in column B and have it show in the corresponding row in column B next to the cell in column A.

So:

A: B: C: D:

Jack Mark 12
Bill "6" Dave 25
Peter Melissa 10
Mary Bill 6
Dave "25" Jack 8

Hmm, guess that doesn't look to good in the actual post. The numbers in "" should be in column B and be the value that is returned after searching column C for the same name as in column A


I want excel to find the corresponding names in column C and return the value for that name in B.

I hope I explained myself enough and hopefully someone can help me, I know this must be possible somehow!

Thanks!

1 reply

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 14, 2010 at 10:50 AM
You want to use VLOOKUP

=VLOOKUP(A1, C:D, 2, false)
0
Thanks,

However excel always shows "N/A" when I use that code.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 14, 2010 at 02:12 PM
Post your workbook at some share site. It works for me Here is a shared site https://authentification.site . Post the link here back
0
ah ok...the #N/A was showing up when the vlookup couldn't find a name in the other list!

It works now, thanks!!

And how would the formula look if column "D" wasn't next to "C"?

Also, because this will be for a longer list, isn't there something I can add to the formula so it won't return "#N/A" when it doesn't find a matching name? Like just leaving it blank or preferably using conditional formatting to change the "new" name in column A?

Again, thanks for your help!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 15, 2010 at 03:06 PM
Not sure what you are asking. May be if i can explain what formula is , you can get to the answer yourself


=VLOOKUP(A1, C:D, 2, false)

A1: Is the value that we are using to find the corresponding value
C:D is the range that we are using to match A1 and find the corresponding value
2: if you notice C and D makes two column. When match is found, it is saying use the 2nd column
false is saying that match has to be exact

Basically in long english the formula is saying look for value A1 in column C and when match is found, use the second column in range C:D ( which would be D) to give me its value
0