Forum Discussion
How to ignore values when searching the data
In F4:
=MINIFS($K$4:$K$18,$H$4:$H$18,"<>"&B4,$L$4:$L$18,C4)
This calculates the lowest MBS for the same SA for products not equal to that in B4.
In E4:
=INDEX($H$4:$H$18,MATCH(1,($L$4:$L$18=C4)*($K$4:$K$18=F4),0))
This looks up the product with the same SA, and with the minimum MBS calculated in F4.
In F4:
=MINIFS($K$4:$K$18,$H$4:$H$18,"<>"&B4,$L$4:$L$18,C4)
This calculates the lowest MBS for the same SA for products not equal to that in B4.
In E4:
=INDEX($H$4:$H$18,MATCH(1,($L$4:$L$18=C4)*($K$4:$K$18=F4),0))
This looks up the product with the same SA, and with the minimum MBS calculated in F4.
Thank you.
I see what you did. Calculated the minimum MBS for the same SA as in C4 that was not the same product as in B4 first, then used that result to calculate the product that is associated with the MBS value.Also, I did not know you could do this within the function, "<>"&B4. Thanks for the education.
