Comparing Columns and returning value

Closed

Werdna - Apr 14, 2010 at 09:34 AM
rizvisa1 Posts 4478 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!

Comparing Columns and returning value
Display two columns in data validation list but return only one - Guide
If cell contains date then return value ✓ - Excel Forum
If cell contains specific text then return value in another cell ✓ - Excel Forum
Excel formula to check if cell contains a date - Excel Forum
If two cells match return value from third excel ✓ - Excel Forum

1 response

Answer 1 / 1

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 14, 2010 at 10:50 AM

You want to use VLOOKUP

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

Werdna
Apr 14, 2010 at 01:03 PM

Thanks,

However excel always shows "N/A" when I use that code.

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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

Werdna
Apr 15, 2010 at 01:55 PM

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!

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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

Similar discussions

Excel - IF function with multiple words

If cell = date return yes or no.

help me to add particular cells

Comparing columns and returning value

Excel: If Date =, then enter a value

Similar discussions

Subscribe To Our Newsletter!