VLOOKUP/IF statement problem

  • Thread starter Thread starter Mark1ace1
  • Start date Start date
M

Mark1ace1

In customer worksheet (Movies), I have a rating such as R 18, R 15..I
J9 is customer name....In worksheet 3 (Customers) I have cust #
Customer name and AGE ( Based on =DATEDIF(D2,NOW(),"y") DOB in Cell
D)...Going back to worksheet one I have assigned a cell called chec
ID...Now what I am trying to achieve is Vlookup that persons date o
birth which corrosponds with their name then if it is <18 due to th
Rating being R 18, it would leave a message called "Check ID" otherwis
"OK" furthermore, if that persons age is <15 when the Rating is R1
then "Check ID" or "OK"...I have tried various ways but my head hurt
now...:(

Mar
 
One way ..

Assuming the set-up is

In Sheet: Movies
--------------------
In cols A and B
data from row2 down

Title Rating
Movie1 R18
Movie2 R15
Movie3 R18
etc

In Sheet: Cust
--------------------
In cols B and C
data from row2 down

Name Age
Cust1 19
Cust2 11
Cust3 17
etc

In Sheet1
------------
The customer's name is input in A2
and the movie's name is input in B2

Put in say C2:

=IF(ISNA(MATCH(A2,Cust!B:B,0)),"No such
Cust",IF(ISNA(MATCH(B2,Movies!A:A,0)),"No such
movie",IF(AND(OFFSET(Movies!$B$1,MATCH(B2,Movies!A:A,0)-1,)="R15",OFFSET(Cus
t!$C$1,MATCH(A2,Cust!B:B,0)-1,)<15),"Check
ID",IF(AND(OFFSET(Movies!$B$1,MATCH(B2,Movies!A:A,0)-1,)="R18",OFFSET(Cust!$
C$1,MATCH(A2,Cust!B:B,0)-1,)<18),"Check ID","OK"))))

C2 will return the desired result, i.e. "Check ID" or "OK"
depending on the inputs made in A2 and B2

Error Traps
--------------
Unmatched customer name or movie name will be indicated
as "No such Cust" and "No such movie" accordingly

If *both* customer name or movie name are unmatched,
the unmatched customer name takes precedence
and C2 will return "No such Cust"
 
Perhaps it's better to wrap a TRIM() around the look-up cells to improve
robustness
in matching the inputs in A2 and B2 in Sheet1 ..

So, replace the formula in C2 with:

=IF(ISNA(MATCH(TRIM(A2),Cust!B:B,0)),"No such
Cust",IF(ISNA(MATCH(TRIM(B2),Movies!A:A,0)),"No such
movie",IF(AND(OFFSET(Movies!$B$1,MATCH(TRIM(B2),Movies!A:A,0)-1,)="R15",OFFS
ET(Cust!$C$1,MATCH(TRIM(A2),Cust!B:B,0)-1,)<15),"Check
ID",IF(AND(OFFSET(Movies!$B$1,MATCH(TRIM(B2),Movies!A:A,0)-1,)="R18",OFFSET(
Cust!$C$1,MATCH(TRIM(A2),Cust!B:B,0)-1,)<18),"Check ID","OK"))))
 
Thanks so much for your reply but that went over my head..lol..

Ok here goes best as I can..

I sheet 1 column G I have ratings...In sheet 1 column J I have custome
names...

In sheet 3 Column B I have customer names. In Column C, I have thei
ages and in column D their date of birth..

Now, great formula but it threw me..I want to check their Date of birt
or age and return an "OK" if over 18 when Renting R 15 or R 18 or Chec
ID, if their birth date is under 18 or under 15...It is my last piec
of my jigsaw...Thank
 
ok, hope I've read you correctly ..

In Sheet1
-----------
Col G = Ratings (i.e. R15, R18 ... )
Col J = Customer names

Data starts from row2 down (assumed)

Put in say, K2:

=IF(ISNA(MATCH(TRIM(J2),Sheet3!B:B,0)),"No such
Cust",IF(AND(G2="R15",OFFSET(Sheet3!$C$1,MATCH(TRIM(J2),Sheet3!B:B,0)-1,)<15
),"Check
ID",IF(AND(G2="R18",OFFSET(Sheet3!$C$1,MATCH(TRIM(J2),Sheet3!B:B,0)-1,)<18),
"Check ID","OK")))

Copy K2 down as many rows as you have customer names in col J

Col K will extract the customers' age from col C of Sheet3
for the customers listed in col J (Sheet1),
check these against the corresponding rating in col G (Sheet1)
and return either "Check ID" or "OK"
 
Back
Top