Did you say I have to use the SHIFT+CTRL+ENTER ??

Yes, it is an array formula.

You can use this longer non-array version (normally entered):

=IF(ISNA(MATCH(TRUE,INDEX(EXACT(E2761,$B$1:$B$2756 ),,1),0)),"No
Match",INDEX($C$1:$C$2756,MATCH(TRUE,INDEX(EXACT(E 2761,$B$1:$B$2756),,1),0)))

--
Biff
Microsoft Excel MVP


"markythesk8erboi" wrote in
message ...
I'm having more problems with it LOL!!!
Did you say I have to use the SHIFT+CTRL+ENTER ??

"T. Valko" wrote:

Ok but will I be able to auto-fill the formula

Yes, just make the references to the table absolute:

=IF(ISNA(MATCH(TRUE,EXACT(E2761,$B$1:$B$2756),0)), "No
Match",INDEX($C$1:$C$2756,MATCH(TRUE,EXACT(E2761,$ B$1:$B$2756),0)))

would you mind explaining how it works?

A
a
AA
Aa
aA
aa

B1 = lookup value = a

EXACT(B1,A1:A6)

This returns an array of either TRUE or FALSE:

A = a = FALSE
a = a = TRUE
AA = a = FALSE
Aa = a = FALSE
aA = a = FALSE
aa = a = FALSE

MATCH returns the relative position of the first TRUE it finds (if it
does
in fact find one) and passes this number to the INDEX function telling it
to
return the value from column C located at positon n.

In this case the first (and only) TRUE is located at position 2. So, if
your
lookup table looked like this:

A...10
a...15
AA...12
Aa...22
aA...19
aa...57

Then the result of the formula is 15 because an EXACT match of "a" was
found in position 2.



--
Biff
Microsoft Excel MVP


"markythesk8erboi" wrote in
message ...
Ok but will I be able to auto-fill the formula or will I have to
re-type
it
for other cells?? And would you mind explaining how it works?


"T. Valko" wrote:

Try this array formula** :

=IF(ISNA(MATCH(TRUE,EXACT(E2761,B1:B2756),0)),"No
Match",INDEX(C1:C2756,MATCH(TRUE,EXACT(E2761,B1:B2 756),0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"markythesk8erboi" wrote
in
message ...
I have a problem that I have not found a suitable solution to:

I have a list of all possible combinations of two letters. (i.e. AA,
Aa,....ZZ,Zz)
NOTE that there are four possibilities for any arangement of the
letters-
AA Aa aA aa

Each of these combinations has a different value assigned to it in
the
next
column.
AA 1
Aa 2
aA 3
aa 4

So, how can I use my table to "lookup" the values of any of the
combinatios?
Also, it should be noted that not ALL of the values in the cells are
two
characters long..... A and a and B and b are in this list
as
well.

What I need to be able to do is simply type into a cell what I want
to
"lookup" and have it spit out the value.

This is formula I have been using but it does not work with all
possibilities.
=IF(EXACT(E2761,VLOOKUP(E2761,$B$1:$C$2756,1,FALSE ))=FALSE,VLOOKUP(E2761,$B$1:$C$2756,2,FALSE),"No
MATCH")

Basically it works but can only return certain values:

"AA" will return a value as will "aa" but "Aa" or "aA" will
not.....
why?