Return offset value after determining min with array formula

[jdfjab]

I'm using the array formula below to determine a minimum value based on two criteria. I'd also like to return a text string that is offset (0,1) from the cell that contains the minimum.

=MIN(IF((Basis="C")*(PriceType=$A343),Multiplier))

Can someone suggest how to go about that.

Thx
jdfjab

 

[Sajan]

Hello @jdfjab,
Welcome to the forum.

Using a technique that Lori shared in the most recent formula challenge, the following array formula is one approach:
=LOOKUP(1,1/FREQUENCY(-9^9, IF((Basis="C")*(PriceType=$A343),Multiplier,9^9)), TextValues)

enter with Ctrl + Shift + Enter

where TextValues is the range of cells next to the Multiplier range.

Cheers,
Sajan.

 

[Smallman]

I am not too sure what the data looks like but won't this work.

=MIN(IF((A$2:A$10="C")*(B$2:B$10=$E$1),C$2:C$10,""))

Array entered. Where Col C is the Col offset one from the range you are looking for. That is assuming you are looking for one whole Columns. A workbook here would be gold.

Or if you were after text

=INDEX($D$2:$D$10, MATCH(MIN(IF((A$2:A$10="C")*(B$2:B$10=E1),C$2:C$10,"")),$C$2:$C$10, 0))

Array entered. I have prepared a file to go with the formula to show the workings.

Take care

Smallman

 

[Sajan]

Hi Smallman,
I think the OP was looking to return a text value based corresponding to the minimum value found in a range... as such, MIN() by itself would not work.

I think your second formula is along the lines of what the OP was looking for.

-Sajan.

 

[Smallman]

Sajan

I did qualify my post.

That is assuming you are looking for one whole Columns

jdfjab was not really clear about what they were after. There was no reference as to if it was a text or numerical value. Didn't say if it was a larger than one column range. I gave them two options and a file. I thought this was a pretty reasonable thing to do based on little information.

Take care

Smallman

 

[jdfjab]

Thank you both for your time and response. I'll check it out first thing in the AM and post my results.

You folks are awesome

 

[jdfjab]

Sajan.
Thank you. Worked perfect.
=LOOKUP(1,1/FREQUENCY(-9^9, IF((Basis="C")*(PriceType=ItemTable[[#This Row],[PPT]]),Multiplier,9^9)), CustPrcType)
Need to spend some time to understand it better, but in the meantime my project can continue.

Smallman,
Thankyou for the file and suggestions. Will work with it when time allows. So much to learn and do, so little time to get it all done.

Can't put into words how much the help is appreciated. Thank you both.

 

[Sajan]

Thanks for the feedback! You are welcome! Glad to help!

-Sajan.