Index Match With Multiple Criteria

[acool]

Hi Everyone,

I am currently trying to create an index formula with multiple criteria in Column P. For this formula I would like to pull in the corresponding value from my table if the value in N is equal to the array in S4:S20, the Value in Column B is equal to the array V3:Y3 and the value in column O falls between the weight Min and Max Columns T +U. For example, if a Product is Large Standard, .0625lbs and has unit identifier of 1 should return a value of $5.75 While I have tried creating this formula in my cells below, I continue to receive errors. Any help in solving this would be greatly appreciated. Thank You!

 

[hagia_sofia]

Hello, try the following (the "$B2=$V$3:$Y$3" part should be part of the "column_num" argument instead of "row_num" argument):

=INDEX($V$4:$Y$20,XMATCH(1,($N2=$S$4:$S$20)*($O2>=$T$4:$T$20)*($O2<=$U$4:$U$20)),XMATCH($B2,$V$3:$Y$3))

 

[Peter_SSs]

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.

Another option would be

=FILTER(FILTER(V$4:Y$20,(S$4:S$20=N2)*(T$4:T$20<O2)*(U4:U20>=O2)),V$3:Y$3=B2)

 

[acool]

Hello, try the following (the "$B2=$V$3:$Y$3" part should be part of the "column_num" argument instead of "row_num" argument):

=INDEX($V$4:$Y$20,XMATCH(1,($N2=$S$4:$S$20)*($O2>=$T$4:$T$20)*($O2<=$U$4:$U$20)),XMATCH($B2,$V$3:$Y$3))

This worked! Thank You!