Entering the wrong profile

Copper Contributor

I have a project to do in metallic structures, and in one of the cases I need to say which profile is approved with the lowest mass, but when I do this it is returning the wrong value. It works as follows (my Excel is in Portuguese, I don't know if they are the same names): the "minimoses" function is used to find the smallest value of a matrix with criteria whether it was approved. For the next step I used the "match" function, when using it I related the result of the previous function so that it could search for the number found in an array that I inserted. The problem is that it returns the position of the number 25.3 and the number reported by the "minimoses" function is 28.3 and thus tells me the wrong metal profile. At first I changed it to "procv" but it wasn't enough to solve it, the solution I found was to add "match" to 7, that way it gives me the right profile. This makes it lose its meaning, since the idea is to automate it and not to be a fixed answer. If anyone can provide a solution or suggestion, I would appreciate it. Sorry if anything is written wrong.Captura de tela 2024-03-02 152522.pngCaptura de tela 2024-03-02 152727.png

4 Replies

@EduMaVie 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar I created the same situation in Google spreadsheet with the same name as the Excel function, if you could try I would appreciate it

 

https://docs.google.com/spreadsheets/d/10VLBcuXKYhj8hZQF4FbMfAhtglpqyFvMfYbjRQMTGE4/edit?usp=sharing

@EduMaVie 

Thanks. In your sample workbook, the formula in J3 should be:

=MINIFS(H4:H62,F4:F62,"APROVADO")

=MÍNIMOSES(H4:H62;F4:F62;"APROVADO")

and the formula in M2:

=INDEX(E4:E62,MATCH(1,(F4:F62="APROVADO")*(H4:H62=J3),0))

=ÍNDICE(E4:E62;CORRESP(1;(F4:F62="APROVADO")*(H4:H62=J3);0))

In the MATCH part, we have to add the condition on APROVADO too. And we have to specify 0 as 3rd argument, because we want an exact match.

See the attached version.

This function was very good, thank you for your help.