multiple column lookups

[Guest]

I need to get a formula for Col D in sheet 1 to tell me that for column A in
Sheet 1
Col B does have a matching TYPE in Sheet 2.
And then, once that is established (or fixed), I need a formula for Col E
which would test both Col A & Col B and confirm that Col C (Cost) matches.


Sheet 1
Col A Col B Col C Col D Col E
Trip Type Cost
A Single $100 Yes $ok
A Quad $400 Yes $ok
B Super $200 NO
C Double $300 Yes $NO

Sheet 2
Col A Col B Col C
Trip Type Cost
A Single $100
A Double $200
A Triple $300
A Quad $400
B Single $100
B Double $200
B Triple $300
B Quad $400
C Single $100
C Double $200
C Triple $300
C Quad $400

 

[just_jon]

First, create a new column on Sheet2 in D, filling with:

=A2&CHAR(1)&B2

Copy down to end of data in A,

In Sheet1, D2 copied down:

=IF(COUNTIF(Sheet2!D,A2&CHAR(1)&B2),"Yes","No")

In Sheet1, E2 copied down:

=IF(D2="Yes",IF(INDEX(Sheet2!C:C,MATCH(A2&CHAR(1)&B2,Sheet2!D,0))=C2,"$OK","$No"),"")