Lookup & If Statement and Table Array

[t01te6]

Hi,

I have so tried to answer this, 5hours now and 5 forums and my brain still can't get my head around it.

I would like to return a number based on two conditions, i.e:

Area	Soil	Class		Area	Soil
Moray	Sandy				Sandy	Mineral
				Moray	1	2
				Perth	4	2

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Assuming above is two tables the first would have the area and soil type in, I want to put a formula in the class cell next to this that would look up the other table find Moray, check to see if the soil is sandy or mineral in the first table and return the site class number associated to it. In this example returning 1, but if I changed area to Perth it would return 4.

Anybody able to help, please.

Thanks

 

[WarfritLive]

Excel 2013 32 bit

	A	B	C	D	E	F	G	H
1	Area	Soil	Class		Area	Soil	Column1
2	Moray	Sandy	1			Sandy	Mineral
3	Perth	Mineral	2		Moray	1	2
4	Moray	Sandy	1		Perth	4	2
5
6
7
8
9
10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Formula in Class column is:
=IF([@Area]<>"",VLOOKUP([@Area],Table2,IF([@Soil]="Sandy",2,IF([@Soil]="Mineral",3)),FALSE),"")