VLOOKUP AND - Multiple Critierias

[Bilingual]

Hi, im working on a sheet where i need to verify whether two columns fullfill a criteria from a table, i use this formula:

=IFERROR(IF(AND(VLOOKUP([@[From zone]];Tabel5;1;FALSE)=[@[From zone]];VLOOKUP([@[To zone]];Tabel5;1;FALSE)=[@[To zone]]);[@[Value 2]];"");0)

However as i need to verify from not only Table5, but also Table6 and Table7, i have problems doing this in the same formula, right now i have solved it by making 3 separate calculated columns and one sum column, but is there a better way?

 

[arthurbr]

You can use an OR statement. But why do you want to LOOKUP a value in the first column of a table? Just use the value

 

[Bilingual]

You can use an OR statement. But why do you want to LOOKUP a value in the first column of a table? Just use the value

Hi Arthurbr, i need to check whether both the to and from zone columns occur in the Table in the separate sheet.

example



[TABLE="width: 500"]
<tbody>[TR]
[TD]To zone:[/TD]
[TD]From zone:
[/TD]
[/TR]
[TR]
[TD]
ALL45[/TD]
[TD]ALL78[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Table

[TABLE="width: 500"]
<tbody>[TR]
[TD]Zone:[/TD]
[/TR]
[TR]
[TD]ALL45[/TD]
[/TR]
[TR]
[TD]ALL46[/TD]
[/TR]
</tbody>[/TABLE]

In this case, the formula will not return any value as the criteria is not fulfilled.

 

[arthurbr]

I understand that but your VLOOKUP boils down to =VLOOKUP(A1,some_range,1,0) which returns A1 or an error
Perhaps =IFERROR(IF(OR(AND(countif(tABEL5,a1),COUNTIF(TABEL5,B1)),AND(COUNTIF(TABEL6,A1),COUNTIF (TABEL6,B1)),ETC....