Prev: Formula for unique records
Next: how to automatically update a second worksheet when data inserted
From: DLB on 16 Feb 2010 22:09
I have a spreadsheet that has a ID number in column A and an airport
identifier in column B in sheet 1. I want to return a value ("1") if the ID
number and airport identifier matches the ID and airport identifier in sheet
2. The problem is that sheet 2 will have multiple matching ID numbers and
multiple airport identifiers (column B and C). For example:

Sheet 1
A B
1021 KSUN (I want to return a "1" if KSUN matches any of the rows that
match the ID of "1021")

Sheet 2

A B C
1021 KABC KSTL
1021 KCMH KMEM
1021 KVNY KSUN

From: T. Valko on 16 Feb 2010 22:41
Try something like this...

=IF(SUMPRODUCT((Sheet2!A$2:A$4=A2)*(Sheet2!B$2:C$4=B2))>0,1,"")

--
Biff
Microsoft Excel MVP


"DLB" <DLB(a)discussions.microsoft.com> wrote in message
news:6B771E2D-221B-45AD-94D9-5D3959AF5902(a)microsoft.com...
>I have a spreadsheet that has a ID number in column A and an airport
> identifier in column B in sheet 1. I want to return a value ("1") if the
> ID
> number and airport identifier matches the ID and airport identifier in
> sheet
> 2. The problem is that sheet 2 will have multiple matching ID numbers and
> multiple airport identifiers (column B and C). For example:
>
> Sheet 1
> A B
> 1021 KSUN (I want to return a "1" if KSUN matches any of the rows that
> match the ID of "1021")
>
> Sheet 2
>
> A B C
> 1021 KABC KSTL
> 1021 KCMH KMEM
> 1021 KVNY KSUN
>


 | 
Pages: 1
Prev: Formula for unique records
Next: how to automatically update a second worksheet when data inserted