Matching one pair of cells with another

[Giselle]

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

 

[Guest]

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]

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