LOOKUP Function returning #N/A some times

[Hazardsneon]

I am working on a calculator for different Electrical Engineering equations. I know that there are tons of calculators out there that do these but I want to do it anyway.

The part that I am getting hung up on is I have an SI prefix chart on one sheet. This shows, for example, that "k" = 1000, "m" = 0.001 etc.

I am using this prefix chart to put multipliers on data in an adjacent column by using data validation to use a drop box to select "k", "m", etc.

I only use that multiplier lookup in the result cell, so the result cell formula is pretty long with many LOOKUP functions to retrieve the correct multiplier for the inputs.

The problem that I am having is that my result only works with some of the multipliers. Any multipliers greater than 1 return #N/A in the resulting cell. Also, if I select "f" for 10^−15, it results in #N/A but if I go to "z" it works fine.

Not sure what I am doing wrong. Any ideas?

[MrShorty]

It is difficult to say without a solid example of your LOOKUP() functions. According to the help file (https://support.office.com/en-us/art...1-369d5e3864cb ), the LOOKUP() function returns N/A when the lookup value is smaller than the first value in the list (and the first value in the list should be the smallest value, since LOOKUP() requires that the lookup values be arranged in ascending order). My first guess is that you have failed/neglected to sort your lookup array/vector in ascending order.

In a different direction, I would guess that you are doing "unit conversions" (centimeters to kilometers kind of thing). If so, you may be interested to know that the built in CONVERT() function can handle many of these unit conversions -- including the proper use of SI prefixes https://support.office.com/en-us/art...d-666c810f9af2

[Hazardsneon]

That is probably my problem. The reference chart is in descending order. I don't have anymore time today but I will try this tomorrow and see if it fixes it.

Thanks for the tip on the CONVERT() function!!!