Hello,
I'm trying to look up Metric Prefixes using their abbreviations. However,
the lookup needs to be case sensitive to distinguish between, for example,
"E" for exa and "e" for dekao or "M" for mega and "m" for milli.
I would suspect that there is a way to do this using the Exact Function in
conjunction with a lookup function, but I can't seem to get anything to work.
I did manage to get a working function by adding a helper column with the
Ascii code, then doing the match on that column, but if there's a way to do
it without the helper, I'd love to see how.

If anyone has any ideas, please let me know.

Thanks.
--
Mike Lee
McKinney,TX USA

Try this:

...........E..........F
1........M........10
2........e..........22
3........m.........15
4........E..........11

A1 = m

=LOOKUP(2,1/FIND(A1,E1:E4),F1:F4)

Result = 15

Biff

"mikelee101" <mikelee101athotmaildotcom wrote in message
...
Hello,
I'm trying to look up Metric Prefixes using their abbreviations. However,
the lookup needs to be case sensitive to distinguish between, for example,
"E" for exa and "e" for dekao or "M" for mega and "m" for milli.
I would suspect that there is a way to do this using the Exact Function in
conjunction with a lookup function, but I can't seem to get anything to
work.
I did manage to get a working function by adding a helper column with the
Ascii code, then doing the match on that column, but if there's a way to
do
it without the helper, I'd love to see how.

If anyone has any ideas, please let me know.

Thanks.
--
Mike Lee
McKinney,TX USA

Wow...worked like a charm. Trying to figure out how it works, because this
is probably a solution that can be used for quite a few lookup problems...It
never occured to me to try Find over a range of cells instead of just on a
text string within a cell...

I think I've figured out (for the most part) what it's doing, but I'm a
little curious about a couple of things, if you wouldn't mind enlightening
me...

First, why 1/Find(....)? Wouldn't you get the same array without the "1/"?
Second, why 2 for the lookup value? It looks like the array is going to be
made up of #VALUE! and 1. Wouldn't 1 work as the lookup value, or does it
need to be something greater than 1 and 2 just happens to fit that bill...?

Thanks again. This is a great solution...
--
Mike Lee
McKinney,TX USA


"T. Valko" wrote:

Try this:

...........E..........F
1........M........10
2........e..........22
3........m.........15
4........E..........11

A1 = m

=LOOKUP(2,1/FIND(A1,E1:E4),F1:F4)

Result = 15

Biff

"mikelee101" <mikelee101athotmaildotcom wrote in message
...
Hello,
I'm trying to look up Metric Prefixes using their abbreviations. However,
the lookup needs to be case sensitive to distinguish between, for example,
"E" for exa and "e" for dekao or "M" for mega and "m" for milli.
I would suspect that there is a way to do this using the Exact Function in
conjunction with a lookup function, but I can't seem to get anything to
work.
I did manage to get a working function by adding a helper column with the
Ascii code, then doing the match on that column, but if there's a way to
do
it without the helper, I'd love to see how.

If anyone has any ideas, please let me know.

Thanks.
--
Mike Lee
McKinney,TX USA

=SUMPRODUCT(--(ISNUMBER(FIND(A1,E1:E4))),F1:F4)


"mikelee101" wrote:

Hello,
I'm trying to look up Metric Prefixes using their abbreviations. However,
the lookup needs to be case sensitive to distinguish between, for example,
"E" for exa and "e" for dekao or "M" for mega and "m" for milli.
I would suspect that there is a way to do this using the Exact Function in
conjunction with a lookup function, but I can't seem to get anything to work.
I did manage to get a working function by adding a helper column with the
Ascii code, then doing the match on that column, but if there's a way to do
it without the helper, I'd love to see how.

If anyone has any ideas, please let me know.

Thanks.
--
Mike Lee
McKinney,TX USA

First, why 1/Find(....)? Wouldn't you get the same array without the "1/"?

Actually, yes you would in this case since FIND returns a number and the
result of FIND will be no greater than 1. I'm just used to using it as
1/..........

It looks like the array is going to be made up of #VALUE! and 1.
Wouldn't 1 work as the lookup value, or does it need to be
something greater than 1 and 2 just happens to fit that bill...?

Yes, it has to be greater than 1 and 2 just happens to fit the bill.

The way LOOKUP works is if no number in the range is greater than the
lookup_value (in this case: 2) then the formula resturns the *LAST* number
in the array that is less than the lookup_value. So, the formula returns the
corresponding value from F1:F4 that matches the position of the last number
in the array 1/FIND(A1,E1:E4). It would look like this:

#VALUE!....10
#VALUE!....22
...............1....15
#VALUE!....11

1 is the last number in the range that is less than the lookup_value of 2.

Biff

"mikelee101" <mikelee101athotmaildotcom wrote in message
...
Wow...worked like a charm. Trying to figure out how it works, because
this
is probably a solution that can be used for quite a few lookup
problems...It
never occured to me to try Find over a range of cells instead of just on a
text string within a cell...

I think I've figured out (for the most part) what it's doing, but I'm a
little curious about a couple of things, if you wouldn't mind enlightening
me...

First, why 1/Find(....)? Wouldn't you get the same array without the
"1/"?
Second, why 2 for the lookup value? It looks like the array is going to
be
made up of #VALUE! and 1. Wouldn't 1 work as the lookup value, or does it
need to be something greater than 1 and 2 just happens to fit that
bill...?

Thanks again. This is a great solution...
--
Mike Lee
McKinney,TX USA


"T. Valko" wrote:

Try this:

...........E..........F
1........M........10
2........e..........22
3........m.........15
4........E..........11

A1 = m

=LOOKUP(2,1/FIND(A1,E1:E4),F1:F4)

Result = 15

Biff

"mikelee101" <mikelee101athotmaildotcom wrote in message
...
Hello,
I'm trying to look up Metric Prefixes using their abbreviations.
However,
the lookup needs to be case sensitive to distinguish between, for
example,
"E" for exa and "e" for dekao or "M" for mega and "m" for milli.
I would suspect that there is a way to do this using the Exact Function
in
conjunction with a lookup function, but I can't seem to get anything to
work.
I did manage to get a working function by adding a helper column with
the
Ascii code, then doing the match on that column, but if there's a way
to
do
it without the helper, I'd love to see how.

If anyone has any ideas, please let me know.

Thanks.
--
Mike Lee
McKinney,TX USA

Biff,
Makes perfect sense (now).

Thanks again for the help. Solution works great, and I would have never
come up with it on my own...

--
Mike Lee
McKinney,TX USA


"T. Valko" wrote:

First, why 1/Find(....)? Wouldn't you get the same array without the "1/"?

Actually, yes you would in this case since FIND returns a number and the
result of FIND will be no greater than 1. I'm just used to using it as
1/..........

It looks like the array is going to be made up of #VALUE! and 1.
Wouldn't 1 work as the lookup value, or does it need to be
something greater than 1 and 2 just happens to fit that bill...?

Yes, it has to be greater than 1 and 2 just happens to fit the bill.

The way LOOKUP works is if no number in the range is greater than the
lookup_value (in this case: 2) then the formula resturns the *LAST* number
in the array that is less than the lookup_value. So, the formula returns the
corresponding value from F1:F4 that matches the position of the last number
in the array 1/FIND(A1,E1:E4). It would look like this:

#VALUE!....10
#VALUE!....22
...............1....15
#VALUE!....11

1 is the last number in the range that is less than the lookup_value of 2.

Biff

"mikelee101" <mikelee101athotmaildotcom wrote in message
...
Wow...worked like a charm. Trying to figure out how it works, because
this
is probably a solution that can be used for quite a few lookup
problems...It
never occured to me to try Find over a range of cells instead of just on a
text string within a cell...

I think I've figured out (for the most part) what it's doing, but I'm a
little curious about a couple of things, if you wouldn't mind enlightening
me...

First, why 1/Find(....)? Wouldn't you get the same array without the
"1/"?
Second, why 2 for the lookup value? It looks like the array is going to
be
made up of #VALUE! and 1. Wouldn't 1 work as the lookup value, or does it
need to be something greater than 1 and 2 just happens to fit that
bill...?

Thanks again. This is a great solution...
--
Mike Lee
McKinney,TX USA


"T. Valko" wrote:

Try this:

...........E..........F
1........M........10
2........e..........22
3........m.........15
4........E..........11

A1 = m

=LOOKUP(2,1/FIND(A1,E1:E4),F1:F4)

Result = 15

Biff

"mikelee101" <mikelee101athotmaildotcom wrote in message
...
Hello,
I'm trying to look up Metric Prefixes using their abbreviations.
However,
the lookup needs to be case sensitive to distinguish between, for
example,
"E" for exa and "e" for dekao or "M" for mega and "m" for milli.
I would suspect that there is a way to do this using the Exact Function
in
conjunction with a lookup function, but I can't seem to get anything to
work.
I did manage to get a working function by adding a helper column with
the
Ascii code, then doing the match on that column, but if there's a way
to
do
it without the helper, I'd love to see how.

If anyone has any ideas, please let me know.

Thanks.
--
Mike Lee
McKinney,TX USA

You're welcome. Thanks for the feedback!

Biff

"mikelee101" <mikelee101athotmaildotcom wrote in message
...
Biff,
Makes perfect sense (now).

Thanks again for the help. Solution works great, and I would have never
come up with it on my own...

--
Mike Lee
McKinney,TX USA


"T. Valko" wrote:

First, why 1/Find(....)? Wouldn't you get the same array without the
"1/"?

Actually, yes you would in this case since FIND returns a number and the
result of FIND will be no greater than 1. I'm just used to using it as
1/..........

It looks like the array is going to be made up of #VALUE! and 1.
Wouldn't 1 work as the lookup value, or does it need to be
something greater than 1 and 2 just happens to fit that bill...?

Yes, it has to be greater than 1 and 2 just happens to fit the bill.

The way LOOKUP works is if no number in the range is greater than the
lookup_value (in this case: 2) then the formula resturns the *LAST*
number
in the array that is less than the lookup_value. So, the formula returns
the
corresponding value from F1:F4 that matches the position of the last
number
in the array 1/FIND(A1,E1:E4). It would look like this:

#VALUE!....10
#VALUE!....22
...............1....15
#VALUE!....11

1 is the last number in the range that is less than the lookup_value of
2.

Biff

"mikelee101" <mikelee101athotmaildotcom wrote in message
...
Wow...worked like a charm. Trying to figure out how it works, because
this
is probably a solution that can be used for quite a few lookup
problems...It
never occured to me to try Find over a range of cells instead of just
on a
text string within a cell...

I think I've figured out (for the most part) what it's doing, but I'm a
little curious about a couple of things, if you wouldn't mind
enlightening
me...

First, why 1/Find(....)? Wouldn't you get the same array without the
"1/"?
Second, why 2 for the lookup value? It looks like the array is going
to
be
made up of #VALUE! and 1. Wouldn't 1 work as the lookup value, or does
it
need to be something greater than 1 and 2 just happens to fit that
bill...?

Thanks again. This is a great solution...
--
Mike Lee
McKinney,TX USA


"T. Valko" wrote:

Try this:

...........E..........F
1........M........10
2........e..........22
3........m.........15
4........E..........11

A1 = m

=LOOKUP(2,1/FIND(A1,E1:E4),F1:F4)

Result = 15

Biff

"mikelee101" <mikelee101athotmaildotcom wrote in message
...
Hello,
I'm trying to look up Metric Prefixes using their abbreviations.
However,
the lookup needs to be case sensitive to distinguish between, for
example,
"E" for exa and "e" for dekao or "M" for mega and "m" for milli.
I would suspect that there is a way to do this using the Exact
Function
in
conjunction with a lookup function, but I can't seem to get anything
to
work.
I did manage to get a working function by adding a helper column
with
the
Ascii code, then doing the match on that column, but if there's a
way
to
do
it without the helper, I'd love to see how.

If anyone has any ideas, please let me know.

Thanks.
--
Mike Lee
McKinney,TX USA