PDA

View Full Version : [SOLVED] Table Lookup without long nested IFs



RINCONPAUL
06-26-2017, 02:44 PM
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
06-26-2017, 03:52 PM
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
06-26-2017, 11:48 PM
Thank you Sir, works a treat.:yes

Bob Phillips
06-27-2017, 02:54 AM
=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
06-27-2017, 03:21 AM
Keep me right Bob!

Bob Phillips
06-27-2017, 03:40 PM
It's a team effort Malcolm :yes

Aflatoon
06-29-2017, 02:26 AM
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
06-29-2017, 05:09 AM
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
06-29-2017, 05:24 AM
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
06-29-2017, 05:33 AM
You're right, of course. I never thought to test it.