MIN inside of VLOOKUP?

[samilynn]

Is it possible to use VLOOKUP with the MIN function?
See example below, I want to (on another worksheet), get the values $0.80 and $4.75 respectively, for the two products listed.
I feel it should be easy, but my brain is stuck!!
Thank you,
Sam

PRODUCT	QTY​	PRICE​	QTY​	PRICE​	QTY​	PRICE​
A123	10​	$1.00​	50​	$0.90​	100​	$0.80​
B444	10​	$5.00​	20​	$4.75​

 

[johnny51981]

I don't think you'll need a VLOOKUP function, at least based on what I can see here.

This should work.
=MINIFS($B3:$G3,B$1:G$1,"Price")

 

[samilynn]

Thanks, Johnny, but the product number is in a separate spreadsheet, and that's where I need to input the formula.

Is there a way to modify the MINIFS so it can work across spreadsheets?

Thanks,

Samantha

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[samilynn]

Thank you for the suggestion, Fluff, it makes sense. I just did that, thanks again

Sam

 

[Fluff]

Thanks for that.
Could you have a situation where the minimum value in a row is the Qty rather than the price?

 

[johnny51981]

The best I can come up right now is

=MIN(VLOOKUP(A2,Sheet1!A2:G3,{3,5,7},FALSE)) and then after putting that in your cell, do a CTRL+SHIFT+ENTER to turn it into an array.

The thing to keep in mind is that the {3,5,7} part is the column numbers, counting left to right, from your first column (the PRODUCT). Each time you get a new Price column added to the end, you will need to add the column number, which the next one would show as {3,5,7,9}. And then do the CTRL+SHIFT+ENTER again.

Their may be an even better way of doing this.

 

[samilynn]

Johnny, that works PERFECTLY!! Thank you so much,

Samantha

 

[Fluff]

Two other options.
If the price will always be the smallest value in the row

=MIN(INDEX(Sheet1!B2:G10,MATCH(A13,Sheet1!A2:A10,0),))

or if the qty could be the lowest value

=AGGREGATE(15,6,INDEX(Sheet1!B2:G10,MATCH(A13,Sheet1!A2:A10,0),)/(Sheet1!B1:G1="Price"),1)