Using Exact() with VLookup or Match

[mikelee101]

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.

 

[T. Valko]

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]

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

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

 

[Teethless mama]

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

 

[T. Valko]

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

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...

 

[mikelee101]

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...

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

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...