Matching one pair of cells with another

Greetings

On sheet1 I have a table in A1:B20 that shows 'acceptable pairs', like this:

a mon
a tue
a sun
b mon
b wed
c mon
c thu
c fri
c sat ...etc

On sheet2, users enter similar values in columns C and D. If users enter
'acceptable pairs' as listed in sheet1, that's OK, but if they enter, say
b tue, I don't want excel to accept the pair.

What's the best way to do this?

much thanks for any ideas

Giselle

you can do a "search" using the match function where in my case a4:a12 is the
column of letters, and b4:b12 is the adjacent column with days, and e5 and f5
contain the desired match. This is an array formula (control+shift+enter)
and will yield #NA if there is no match

=MATCH(1,(A4:A12=E5)*(B4:B12=F5),0)

"Giselle" wrote:

> Greetings
>
> On sheet1 I have a table in A1:B20 that shows 'acceptable pairs', like this:
>
> a mon
> a tue
> a sun
> b mon
> b wed
> c mon
> c thu
> c fri
> c sat ...etc
>
> On sheet2, users enter similar values in columns C and D. If users enter
> 'acceptable pairs' as listed in sheet1, that's OK, but if they enter, say
> b tue, I don't want excel to accept the pair.
>
> What's the best way to do this?
>
> much thanks for any ideas
>
> Giselle
>
>
>

hi duane and all

so... are you suggesting I use theCSE formula
=MATCH(1,(A4:A12=E5)*(B4:B12=F5),0) as a validation criteria? If not,
where would you use this?

TQ in advance, Giselle


"duane" <[email protected]> wrote in message
news:[email protected]...
> you can do a "search" using the match function where in my case a4:a12 is
> the
> column of letters, and b4:b12 is the adjacent column with days, and e5 and
> f5
> contain the desired match. This is an array formula (control+shift+enter)
> and will yield #NA if there is no match
>
> =MATCH(1,(A4:A12=E5)*(B4:B12=F5),0)
>
> "Giselle" wrote:
>
>> Greetings
>>
>> On sheet1 I have a table in A1:B20 that shows 'acceptable pairs', like
>> this:
>>
>> a mon
>> a tue
>> a sun
>> b mon
>> b wed
>> c mon
>> c thu
>> c fri
>> c sat ...etc
>>
>> On sheet2, users enter similar values in columns C and D. If users enter
>> 'acceptable pairs' as listed in sheet1, that's OK, but if they enter, say
>> b tue, I don't want excel to accept the pair.
>>
>> What's the best way to do this?
>>
>> much thanks for any ideas
>>
>> Giselle
>>
>>
>>