IF, VLOOKUP, and Matching in Same Formula?

[rjd123]

I can accomplish part of what I am trying to do, but not all. I wonde
if anyone has any ideas?

I have a VLOOKUP formula that can tell me if an item on List 2 is o
List 1. I am using this as the forumla:

=IF(C2=VLOOKUP(C2,B:B,1),"Yes","No")

Serial # List 1 List 2 Is List 2 on List 1?
123000 FL19123 FL19123 Yes
234000 FL19124 FL19124 Yes
456000 FL19125 FL19126 No

In Column A is a list of serial numbers that match the same row i
Column B, List 1. Instead of returning a Yes or No to my query i
Column D, I would like to return the serial number of the line item i
column B.

Serial # List 1 List 2 Is List 2 on List 1,and if so what is the seria
number?

Would anyone know what the formula would be?

Many thanks,
rjd12

 

[David McRitchie]

Hi rjd123.14682w @ excelforum-nospam.com,

It's late I too tire to follow your question. If your argument
is to the left of what you want to return you can use VLOOKUP
and I take it you want an Exact Match so that is fine take a
look at http://www.mvps.org/dmcritchie/excel/vlookup.htm

 

[Aladin Akyurek]

=IF(ISNUMBER(MATCH(C2,$B$2:$B$4,0)),C2,"")

 

[rjd123]

I'm still not able to figure it out. Maybe what I was describing wa
hard to understand....

I have have a worksheet that has a list of computers and their seria
numbers. In Column A is the serial number and in Column B is the nam
of the computer.

I have another worksheet that has just a list of computer names, an
it's a much longer list. I know that some of these computers can b
found in the worksheet that has both the computer names and seria
numbers. I need to find the serial numbers for them.

What I've done is just paste the long list from the second workshee
into the first worksheet in Column C. I have a VLookup formula tha
will tell me if any of the Column C computers also appear in Column B
It returns "Yes" if there is a match. How do I get it to tell m
instead of "Yes" the serial number of that computer name?

Many thanks,
rjd12

 

[Ragdyer]

If your "yes" and "no" lookup formula is occupying column D, enter this in
column E and drag down to copy as needed:

=INDEX($A$1:$A$100,MATCH(C1,$B$1:$B$100,0))

You'll get #N/A errors when there is no match found in B.
Also, if there are duplicate entries in B, only the *first* entries serial
number from A will be returned.

 

[rjd123]

Ragdyer,

That worked! Thank you so much! I can't even begin to tell you how lon
I've been trying to figure that out. I tweaked your formula slightly s
it would start on row 2 instead of 1 so I can have a header row, and i
still worked perfectly.

I really appreciate your help!

Regards,
rjd12

 

[Ragdyer]

The hardest part of answering questions that are acceptable to the OP
(original poster - you), is *understanding* exactly what they're looking
for.

I looked at your post last night and went right by it, not really
understanding what you were asking (maybe it was just late, see David's
post).

Reading your second explanation today, made it clearer, where any one here
could easily have provided you with the same or similar solution, and if it
wasn't Sunday, you probably would have received 5 or 6 more suggestions (on
the second explanation).

It's really an elementary FAQ.

Anyway, thanks for the feed-back.

 

[rjd123]

Ragdyer,

Yes, I agree that my first post was not as clear as it could have been
I'll remember that for next time. Thank you again.

Regards,
rjd12