Home |
Search |
Today's Posts |
#1
|
|||
|
|||
match colC to ColA, put row from ColB for 'answer'
This is somewhat of a followup to my post "does item match any in column B",
and was answered by bj and Gary's STudent. But, I have some followup to this I was hoping to learn. Thanks to the last couple of answers people helped me with, I'm able to do pretty much what I want with matching these scenarios, but now I was wondering what the possibility would be in regards to, in using a helper column, the following scenario. If I have data in column A and Column B -- and for an example to not be so vague, let's say that each field in Column A is a customer name, and column B is their corresponding customer number. Now, I've brought a handful of names over and I put them in column C. Using Column D as a helper column and this formula: =IF(COUNTIF(A:A,C3)0, "Duplicate", "") I can find out how many of the records are already in column A. But, moving on, I'd like to see about pulling the customer ID from column B if there is a match, and putting that in column D. I just don't know what to put in the function to get this to happen... if I had the time, I'd go searching through the Excel help and other sites, and that would be more fun ultimately, but alas, I'm hoping you or someone can help. Again, I'd be grateful for whatever help folks can provide. Thank you. matt |
#2
|
|||
|
|||
There may be simpler formulas but try this: Assuming that your original data is in the range A2:B6 and your matching names are in cells C2:C6. In cell D2 enter this formula: =IF(ISERROR(INDEX(A$2:B$6,MATCH(C2,A$2:A$6,0),2)), "",INDEX(A$2:B$6,MATCH(C2,A$2:A$6,0),2)) Then copy the formula down through cell D6. -- neopolitan ------------------------------------------------------------------------ neopolitan's Profile: http://www.excelforum.com/member.php...nfo&userid=611 View this thread: http://www.excelforum.com/showthread...hreadid=474127 |
#3
|
|||
|
|||
Hi, Not sure this is what your looking for but try this: =IF(COUNTIF(A:A,C3)0,VLOOKUP(C3,A3:B10,2,0),"") A3:B10 being the range containing the names and id numbers. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=474127 |
#4
|
|||
|
|||
I made a logical error copying that forumula over originally, but when I
figured that out it worked. And yes, that's doing exactly what I wanted... thanks. "pinmaster" wrote: Hi, Not sure this is what your looking for but try this: =IF(COUNTIF(A:A,C3)0,VLOOKUP(C3,A3:B10,2,0),"") A3:B10 being the range containing the names and id numbers. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=474127 |
#5
|
|||
|
|||
Hi Glad it works for you, and thanks for the feedback! Regards! JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=474127 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Match and Concatenate ?? | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Lookup value in colA whos row matches row of index value in colB | Excel Worksheet Functions | |||
Lookup value in colA whos row matches row of index value in colB | Excel Worksheet Functions |