VLOOKUP or MATCH or Magical function

[PJS]

Hello,
I am running into a problem and I am not sure which function to use.
Suppose I have a worksheet1 with of data i.e. the person and the brand of
cars they own.

Person Car
A BMW
A Ford
B GM
B Mercedes
B Porsche
C Honda
D Toyota
D Lexus

Is it possible to create a second worksheet ~ say worksheet 2 and depending
on the data from worksheet 1, it would fill out Y, N

Person Car Own (Y/N)
A BMW Y
A Ford Y
A GM N
A Mercedes N
A Porsche N
A Honda N
A Toyota N
A Lexus N

Thank you for your time!

PJS

 

[Sheeloo]

Assuming you want Y/N in Col I and values to be looked up are in Col F & G
(second set of your data)
Enter this where you want Y/N
=IF((VLOOKUP(F2,A:B,2,FALSE)=G2),"Y","N")

This assumes your first set of data is in Col A & B...

It does not check for errrors. You may like to wrap the above in a ISNA
formula...

 

[Sheeloo]

Sorry, my solution will not work...

I did not consider multiple occurrences in Col A...

 

[Sheeloo]

One way is to combine col A & B in a helper column C (=A&B) and then use this
=IF((VLOOKUP(F2&G2,C:C,1,FALSE)=G2),"Y","N")

 

[bpeltzer]

=if(sumproduct(--(Sheet1!$A$1:$A$100=A2),--(Sheet1!$B$1:$B$100)=B2)=0,"N","Y")
Enter that in Sheet2!C2 and copy it down to fill out your table on Sheet2.
The 100s can be changed to the final row of your data on Sheet1.

 

[Dave Peterson]

=IF(ISNUMBER(MATCH(1,(Sheet1!A1:A10=A1)*(Sheet1!B1:B10=B1),0)),"yes","no")

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

Assumes that the table is on Sheet1 in A1:B10 and that the values to match are
in A1 and B1 of a different sheet.

 

[ShaneDevenshire]

Hi,

The answer may be yes, but without you telling us what condition will
determine whether to display yes or no we are all just taking a guess at what
formula you need is. Look at the different answers you have recieved, each
is a shot in the dark. Please provide us with more information.

 

[Max]

Try also a pivot table. It'll deliver a 99.99% close fit results in a matter
of seconds with a few clicks, drags n drops ..

Some easy steps to lead you in (Excel 2003):
Select any cell within the source table
Click Data > Pivot table
Click Next > Next

In step 3 of the wiz, click Layout:
Drag n drop "Person" in ROW area
Double-click on it, set Subtotals to None

Drag n drop "Car" in ROW area
Double-click on it, check "Show items with no data"

Drag n drop "Car" in DATA area (it'll appear as Count)
Click OK > Finish. That's it!
Hop over to the pivot sheet (to the left) for the results
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300 Files:361 Subscribers:57
xdemechanik

 

[PJS]

Thank you everyone, I will try the different formulas and see which one works
the best.

Thanks again!!!