Comparing Columns and returning value

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-


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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You want to use VLOOKUP

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

However excel always shows "N/A" when I use that code.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Post your workbook at some share site. It works for me Here is a shared site https://authentification.site . Post the link here back
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!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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