How do I use case sensitive VLOOKUP?

[markythesk8erboi]

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?

 

[Niek Otten]

You can build a new character string for both the search argument and the keys (new column) in your lookup table (I used the ASCII
code, but any code will do):

=CODE(LEFT(A1,1))&IF(LEN(A1)=1,"",CODE(RIGHT(A1,1)))

Use that string in your VLOOKUP formula

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[T. Valko]

Try this array formula** :

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

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

 

[markythesk8erboi]

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?

 

[markythesk8erboi]

LOL! I'm sorry I dont get it... ASCII???....and where do I insert it into the
formula???

 

[Niek Otten]

I assume your table is in A1:B5, and the code you want to look up is in C1

Insert a new column between A and B (that is, a new B column)
In B1, enter:
=CODE(LEFT(A1,1))&IF(LEN(A1)=1,"",CODE(RIGHT(A1,1)))
and fill down
In the meantime, C1 has become D1, because you inserted a column
In E1, enter:
=CODE(LEFT(D1,1))&IF(LEN(D1)=1,"",CODE(RIGHT(D1,1)))

Now your lookup formula is:

=VLOOKUP(E1,B1:C5,2,FALSE)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| LOL! I'm sorry I dont get it... ASCII???....and where do I insert it into the
| formula???
|
|
| "Niek Otten" wrote:
|
| > You can build a new character string for both the search argument and the keys (new column) in your lookup table (I used the
ASCII
| > code, but any code will do):
| >
| > =CODE(LEFT(A1,1))&IF(LEN(A1)=1,"",CODE(RIGHT(A1,1)))
| >
| > Use that string in your VLOOKUP formula
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | > |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?
| > |
| >
| >
| >

 

[T. Valko]

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.

 

[markythesk8erboi]

I'm having more problems with it LOL!!!
Did you say I have to use the SHIFT+CTRL+ENTER ??

 

[T. Valko]

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(E2761,$B$1:$B$2756),,1),0)))