Associate value with text

[andres91]

Hi. I am a novice user in Excel and I am trying to find dental color. For that, there are standard colors (16 standard colors), which are expressed as A1, A2, A3, etc. Each of these colors has a Lab value (a color space similar to RGB), as expressed in the attached table.

Now there is an E DELTA formula, which is expressed as:

delta-e-equation.jpg

This formula compares the Lab values of the initial color to compare with the Lab of the standard colors. The color with lower DELTA E corresponds to the tooth color.

I have managed in Excel to calculate the 16 DELTA E and find the minimum DELTA E resultant, but I do not know how to associate that lower DELTA E value with the standard color in a cell.

Can you do it instead of looking visually?

Thanks!!

PS.: In the example, I know that 4.64 value is MIN so my desired shade is A4, but i want to do that automatically.

[sandy666]

maybe this: =MIN(SQRT((G3-C4:C19)^2+(H3-D4:D19)^2+(I3-E4:E19)^2)) array entered

[andres91]

Wow, that's more efficient, but i need to associate that min value with column B and show that in a cell.

Example: that min value 4.64 correspond to A4 because they correspond to the same row.

[sandy666]

something like that?
=INDEX($B$4:$B$19,MATCH($H$7,$K$4:$K$19,0))
or
=INDEX($B$4:$B$19,MATCH(MIN(SQRT((G3-C4:C19)^2+(H3-D4:D19)^2+(I3-E4:E19)^2)),$K$4:$K$19,0)) array entered

(I changed layout so formulas here and in the file are not the same but similar )

[andres91]

Thanks for replying. Why the formula has $ ? I tried to enter and it says it's an error in formula.

żIn spanish it would be:

=INDICE(B4:B19,COINCIDIR(MIN(RAIZ((G3-C4:C19)^2+(H3-D4:D19)^2+(I3-E4:E19)^2)),K4:K19,0))

[andres91]

Thanks for replying. Why the formula has $ ? I tried to enter and it says it's an error in formula.

żIn spanish it would be:

=INDICE(B4:B19,COINCIDIR(MIN(RAIZ((G3-C4:C19)^2+(H3-D4:D19)^2+(I3-E4:E19)^2)),K4:K19,0))

but it says error.

Sorry my novice

[sandy666]

what kind of error ? #N/A?
I said: array entered = Control+Shift+Enter
but try this: =INDEX($B$4:$B$19,MATCH(MIN(SQRT((H3-C4:C19)^2+(I3-D4:D19)^2+(J3-E4:E19)^2)),SQRT((H3-C4:C19)^2+(I3-D4:D19)^2+(J3-E4:E19)^2),0)) Control+Shift+Enter

es: =INDICE($B$4:$B$19;COINCIDIR(MIN(RAIZ((H3-C4:C19)^2+(I3-D4:D19)^2+(J3-E4:E19)^2));RAIZ((H3-C4:C19)^2+(I3-D4:D19)^2+(J3-E4:E19)^2);0)) Control+Shift+Enter
on my attachment

ps. I don't know how is: Control, Shift, Enter in Spanish

[andres91]

Wow!!! Thanks, that really works. I see my error was writing "," instead of ";" after B4:B19. I didn't know about array. I tried without control+shift+enter and it said N/A, like you said. Now I know about arrays.

Thanks again!!

[sandy666]

You are welcome

If that takes care of your original question, please click Add Reputation then select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.