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 ?

 

[snjpverma]

why do you quote other people and write within the quote? How are we suppose to know which post/words you are referring to?

When you quote someone, you need to write your comment outside of the quote i.e. after this "[/QUOTE]"

Now, since we don't know which post you have quoted, we won't be able to give you a modification on the formula.
Please post with proper quotes and write your comments after it, so the members can suggest solutions accordingly.

 

[Pori96]

why do you quote other people and write within the quote? How are we suppose to know which post/words you are referring to?

When you quote someone, you need to write your comment outside of the quote i.e. after this "

"

Now, since we don't know which post you have quoted, we won't be able to give you a modification on the formula.
Please post with proper quotes and write your comments after it, so the members can suggest solutions accordingly.
[/QUOTE]


Ok, got it. Sorry, I'm new...

 

[snjpverma]

That's ok. This time you did it correctly. Good to see you learnt it now.

 

[Fluff]

How are we suppose to know which post/words you are referring to?

Simply click on the username at the top of quote & it will take you to the post in question.

 

[snjpverma]

I was confused because only the name was reflecting and the person had more than one post.
But this is a nice trick ,Fluff. Thanks

 

[Fluff]

 

[Pori96]

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)

Done! I made it work with a vlookup and your formula. Thank you so much!

 

[jasonb75]

I made it work with a vlookup and your formula.

Would you be so kind as to post the formula please.