match colC to ColA, put row from ColB for 'answer'

MatthewTap · 10-07-2005, 10:05 AM

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

**neopolitan** · 10-07-2005, 02:49 PM

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.

pinmaster · 10-07-2005, 07:36 PM

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

MatthewTap · 10-14-2005, 11:05 AM

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
>
>