Lookup with multiple criteria

[All2Cheesy]

Hello all,

I'm trying to find a value in an array using multiple criteria.


Basically, what I would be looking to achieve in the below example would be to search for A2 in E1:E4, where B2 is also greater than G2:G4, and less than H2:H4 and return the answer from F2:F4

This should output Zone 2

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Company[/TD]
[TD]Postcode[/TD]
[TD][/TD]
[TD][/TD]
[TD]Comapny[/TD]
[TD]Zone[/TD]
[TD]Postcode Range 1[/TD]
[TD]Postcode Range 2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Test123[/TD]
[TD]3015[/TD]
[TD][/TD]
[TD][/TD]
[TD]Test123[/TD]
[TD]Zone 1[/TD]
[TD]3000[/TD]
[TD]3010[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Test123[/TD]
[TD]Zone 2[/TD]
[TD]3011[/TD]
[TD]3020[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Test234[/TD]
[TD]Zone 1[/TD]
[TD]4000[/TD]
[TD]4030[/TD]
[/TR]
</tbody>[/TABLE]


Any assistance is greatly appreciated.

 

[Gerald Higgins]

OK, and what exactly should the answer be, with that input data ?

Should it be
Zone 2
from line 3 ?

 

[tyija1995]

Hey,

Try:
=INDEX(F2:F4,MATCH(1,(A2=E2:E4)*(B2>G2:G4)*(B2 < H2:H4),0))

<h2:h4),0))
Ctrl+Shift+Entered

Result gives Zone 2

OR perhaps if you don't want an array formula:

=LOOKUP(2,1/((E2:E4=A2)*(B2>G2:G4)*(B2 < H2:H4)),F2:F4)</h2:h4),0))

 

[All2Cheesy]

That looks like it's worked, thank you.