Previous Topic Next Topic

Comparing Columns and returning value

Closed
Werdna - 14 Apr 2010 à 09:34
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 15 Apr 2010 à 15:06


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 response

Réponse 1 / 1
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
14 Apr 2010 à 10:50
You want to use VLOOKUP

=VLOOKUP(A1, C:D, 2, false)
Werdna
14 Apr 2010 à 13:03
Thanks,

However excel always shows "N/A" when I use that code.
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
14 Apr 2010 à 14:12
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
15 Apr 2010 à 13:55
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 28 January 2010 Status Contributor Last seen 5 May 2022 766
15 Apr 2010 à 15:06
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

Compare values in two columns and return the value from third
ramdubai -
keshav -

11 responses
Comparing columns and returning value
ll -
kittu -

35 responses
Looking For a Value in a Cell
Tom9o -
rizvisa1 -

3 responses
Fill adjacent cell with number from text answer
MitchP -
vcoolio -

20 responses