VLOOKUP or MATCH or Magical function
 

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

VLOOKUP or MATCH or Magical function
 

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

"PJS" wrote:

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

VLOOKUP or MATCH or Magical function
 

Sorry, my solution will not work...

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

"PJS" wrote:

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

VLOOKUP or MATCH or Magical function
 

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


"Sheeloo" wrote:

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

"PJS" wrote:

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

VLOOKUP or MATCH or Magical function
 

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

"PJS" wrote:

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

VLOOKUP or MATCH or Magical function
 

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

PJS wrote:

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

--

Dave Peterson

VLOOKUP or MATCH or Magical function
 

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.

--
Thanks,
Shane Devenshire


"PJS" wrote:

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

VLOOKUP or MATCH or Magical function
 

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" wrote:
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

Wow Thank you for the suggestions!
 

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

Thanks again!!!