Index &match formula not working properly

[vmohan1978]

Hi,

In the attached file , the formula in cell "EY22" is not working properly . It's taking the value of particular column only .
Also, help me with formula with minimum quantity( >0) and respective vendor name.

 

[Ramesh]

Hey Mohan,

With regards to the Index and Match function, when I place the absolute match parameter for the match function it is returning the output:

In cell EY22, your formula should be written as follows:

=INDEX($I$15:$EU$15,MATCH(EX22,I22:EU22,0))

When I applied the same for the rest of the cells to the value is changing based on the match..

And with regards to the second one, I use MinIf in Excel 2016 or Min(If()) array formula in other versions of excel if the data range is continuous.. not sure how to handle the same in segregated cell references..

 

[vmohan1978]

Hey Mohan,

With regards to the Index and Match function, when I place the absolute match parameter for the match function it is returning the output:

In cell EY22, your formula should be written as follows:

=INDEX($I$15:$EU$15,MATCH(EX22,I22:EU22,0))

When I applied the same for the rest of the cells to the value is changing based on the match..

And with regards to the second one, I use MinIf in Excel 2016 or Min(If()) array formula in other versions of excel if the data range is continuous.. not sure how to handle the same in segregated cell references..

 

[vmohan1978]

Ramesh,

Thanks for the help. It worked properly.
If anyone helps on second part, it would be great help.

 

[Khalid NGO]

Hi Mohan,

This can be used for non-contiguous cells, minimum value and greater than zero:

=SMALL((I22,AF22,BF22,CE22,DD22,EC22),1+FREQUENCY((I22,AF22,BF22,CE22,DD22,EC22),0))

Found here

Regards,

 

[vmohan1978]

Thank you very much khalid.