Table Lookup without long nested IFs

[RINCONPAUL]

In the attachment I want to return the text in col D if a match for F2 is found in col A and the value in G2 is >= the value in col B and <= the value in col C. I can do it with a huge table index match and long nested If statement, but hoping there's a way to simplify things? If there is no match the function would return an Error#.

[mdmackillop]

This seems to work if not written in Rows 2-17 where the corresponding row value appears instead of an error.
=INDEX(D2 : D17,SUMPRODUCT(--(A2:A17=F2),--(G2>=B2:B17),--(G2<=C2:C17),ROW(A1:A16)))

otherwise
=IF(SUMPRODUCT(--(A2:A17=F2),--(G2>=B2:B17),--(G2<=C2:C17),ROW(A1:A16))=0,#N/A,INDEX(D2 : D17,SUMPRODUCT(--(A2:A17=F2),--(G2>=B2:B17),--(G2<=C2:C17),ROW(A1:A16))))

[RINCONPAUL]

Thank you Sir, works a treat.:yes

[Bob Phillips]

=IF(SUMPRODUCT(--(A2:A17=F2),--(G2>=B2:B17),--(G2<=C2:C17),ROW(A1:A16))=0,#N/A,INDEX(D2:D17,SUMPRODUCT(--(A2:A17=F2),--(G2>=B2:B17),--(G2<=C2:C17),ROW(A1:A16))))

You can simplify this

=IFERROR(INDEX(D2:D17,SUMPRODUCT(--(A2:A17=F2),--(G2>=B2:B17),--(G2<=C2:C17),ROW(A1:A16))),NA())

[mdmackillop]

Keep me right Bob!

[Bob Phillips]

It's a team effort Malcolm :yes

[Aflatoon]

I'm not sure that shorter function works properly. If you have 3 in F2 and -32 in G2, it should (I think) return an error, not LAY, which it does now because it's using 0 as the index row value.

I'd suggest this instead:

=LOOKUP(2,1/(A2:A17=F2)/(G2>=B2:B17)/(G2<=C2:C17),D2:D17)

[mdmackillop]

Hi Aflatoon
I referred to the short function limitation in Post 2.
I've not seen Lookup used in that manner before. Are there other uses for 1/array you know of?
Regards
MD

[Aflatoon]

I was referring to Bob's shortened version, which has the same problem.

No, I only use it for multi-criteria lookups. Where there are multiple matches, it returns the last matching value, unlike an array form of MATCH. A SUMPRODUCT version would return an incorrect result in that situation.

[mdmackillop]

You're right, of course. I never thought to test it.