Complex Excel Formula

[Pori96]

Hello friends.

I'm struggling with a formula and I was wondering if you could help me with it. I'm trying to match the value of a cell with the heading of a column and the value that I want to show is in the same row where I wrote the formula. The formula works but the problem is that in some cases the cell is empty so it gives me the first value it finds. I need to locate a value that is greater than 0 but also the minimum value that matches the cell with the heading.

Let me show you what I have so far and thank you in advance.

Where should I add the min formula and the if greater than 0 ?

 

[Pori96]

I want to ble able to match the G column with a similar value that matches the heading and delivers the lowest price that is greater than 0 (there are many more columns with different MOQs on the left and on the right but some are blank so as you can see I get 0)

 

[snjpverma]

Could you use the XL2BB tool and give a copy-able format of your data? The XL2BB tool can be seen just at the right side of the post window setting. I have highlighted the same in the below picture.

Also, you have written that there are some blanks on the left and right. If not your actual, then could you give a small sample of your data with the desired output, so that we can clearly see what you want?
You haven't given a output that you desire in the above posts.

 

[Pori96]

Thanks for replying.

Basically I work in a manufacturing company. We have different suppliers and each supplier has a different price based on the MOQ (Minimum order quantity)
In a blank cell I want to write the demand for that month and with this formula I want to locate the best price for that demand.

Example:

This month I have a demand of 50,000 parts (column G).

As you can see some of my suppliers don't have a price for that item because they don't sell it.

But supplier 2 offers that item and it has different prices depending on the MOQ

Let's say that in this case I only have 2 suppliers and as you can see the price per item for an MOQ of 50,000 is $0.0065

I want that number to appear on the column H because that's the best price for the demand I currently have.



Sorry I couldn't install the plug in. My computer blocks it but I hope this helps you with what I want to know.

Thank you for your support.

 

[jasonb75]

Assuming that the quantity in G47 will always be equal to one of the quantities in R47:BT47, then you could try

Excel 2019 (or newer, including office 365)
=MINIFS($R$3:$BT$3,$R$3:$BT$3,">0",R47:BT47,G47)

Excel 2010 - 2016
=AGGREGATE(15,6,(R47:BT47=G47)/(1/$R$3:$BT$3),1)

That's as good as it gets when there are only screen captures to work with.

 

[Pori96]

=MINIFS($R$3:$BT$3,$R$3:$BT$3,">0",R47:BT47,G47)

Thanks but the result is still 0

 

[AhoyNC]

Maybe something like this:
Change ranges to match your data.

Book1
	B	C	D	E	F	G	H	I
1			1000	2000	3000	5000	8000	10000
2	5000	0.0439	0.0981	0.1721	0.0500	0.1109	0.0618	0.1019
3			0.0686	0.0652	0.1266	0.0534	0.1345	0.1173
4			0.0509	0.0931	0.0832	0.0000	0.1566	0.1047
5			0.1500	0.0870	0.0401		0.0970	0.0795
6			0.0978	0.1169	0.1682	0.0439	0.1659	0.1083
Sheet1

Cell Formulas
Range	Formula
C2	C2	=MINIFS(INDEX($D$2:$I$6,0,MATCH($B$2,$D$1:$I$1)),INDEX($D$2:$I$6,0,MATCH($B$2,$D$1:$I$1)),">0")

 

[jasonb75]

Thanks but the result is still 0

Taking another look I think that I got the ranges reversed. Remember that the formulas assume that the quantity in G47 will always be equal to one of the quantities in R3:BT3, if the quantity is not an exact match then it will need a different approach. We can only work with the information you give us.

=MINIFS(R47:BT47,R47:BT47,">0",$R$3:$BT$3,G47)

=AGGREGATE(15,6,1/(R47:BT47/($R$3:$BT$3=G47)),1)

 

[Pori96]

Maybe something like this:
Change ranges to match your data.

The problem is that you can't change the format because the item that is in a row has multiple prices, if you put the prices in a column instead of the row then you will only be able to have one price per item but that's not the case.

 

[Pori96]

Yes! It works! Thanks just one more thing. It only shows me the value if it's an exact match. How do I do it if I want the nearest match?