Problem with IFS/AND/VLOOKUP formula; unexpected behaviour

[appreciatethehelp]

In the attached document the formula in ‘Notation’ sheet cell B3 is eliciting different results to those expected; I would expect the current scenario, achieved via the ‘Full number lists’ sheet cells referenced in the formula, to elicit a TRUE result, instead I’m getting an #NA result.

If someone could please weigh in advising why this is the case, that would be fantastic.

There are other unexpected outcomes, such as FALSE rather than #NA, but I’m hoping that resolving this first issue will lead to the resolution of these other problems as well.

Create randomised number list in groups 7-1 , return red format for entire groups (4 or less) when all are odd (col. B).ods (12.5 KB)

[Villeroy]

=ISNA(lookupformula) returns TRUE if [V|H]LOOKUP or MATCH returns #NA and FALSE otherwise.
=NOT(ISNA(lookupformula)) switches TRUE and FALSE.

[mikekaganski]

I fail to see what the IFS is supposed to give, and how its “result1” could make sense, in Notation.B4

=IFS(VLOOKUP(1;$'Full number lists'.$C1:$'Full number lists'.$C7;1;0)=1;VLOOKUP(1;$'Full number lists'.$C1:$'Full number lists'.$C7;1;0)=2)