VLOOKUP exact match (case sensitive) problem/Question

[ColumbiaDiver]

Hi all,

I’m having a problem with VLOOKUP in Excel 2003 and was hoping someone might have an idea how to resolve it or at least point me in the right direction.

Our HR department utilizes two different systems for tracking staff time based on cost center and division. The data goes from a client server application to an IBM AS/400. I have a RPG program that does a conversion from the client server application to our HR system and I need to combine information from both systems into an Excel spreadsheet.

For example on the client server side the software uses the code Sc to specify clinical Information when the data file is imported to the AS/400 a program is run against the data file that says wherever you see code Sc in the client server data file change it to division 70 cost center 830. Once the entire file has been changed it then moves the data into the AS/400 payroll data.

Our HR department needs an Excel worksheet that contains the following:
1. The alpha division/cost center
2. The associated numeric division/cost center
3. The cost center description
4. Staff hours for that division and cost center.

The problem is that the combined information doesn’t exist on either system in its entirety.

In order to work around this issue I am trying the following:

I extracted the cost center information from the AS/400 translation table which contains the alpha cost center/division, the numeric costs center/division it equates to and the cost center description which is dumped into a worksheet I named Codes. Using the above example:

Column 1 = Sc (alpha cost center/division)
Column 2 = clinical Information (cost center/division description)
Column 3 = 70870 (numeric cost center/division)

I named this range Codes


I then have a second worksheet name Report that contains the following:

Column A = Employee name
Column B = Date worked
Column C Division (alpha)
Column D = Cost Center (alpha)
Column E = columns C and D, combined (which I created)
Example column C contains S and Column D contain c, so column E contains Sc.
Column E = Department. This is where I use my first VLOOKUP to pickup the department description from the Codes worksheet referenced above.

The formula is: =IF(EXACT(E2,VLOOKUP(E2,Codes,1,FALSE))=TRUE,VLOOKUP(E2,Codes,2,FALSE),"No Exact Match")

Cell E2 contain the code Sc which on the Codes worksheet equals CLINICAL INFORMATION for the description. Using the above VLOOKUP Column E should populate the description with the description from the codes worksheet, (CLINICAL INFORMATION) but it returns No Exact Match. On the next cell E3 the code is SC which on the codes worksheet equals CAFETARIA correctly populates the Department column.

This only happens in instances where the same code exists on the Codes worksheet in both upper and lower case. For example SC and Sc, IC and Ic etc. In all instances where the code does not exist in both upper/lower case the VLOOKUP returns the correct value.

I’ve searched the net trying to see what I’m missing, but from everything I can gather this should work.

Sorry for the length of the post, I’d rather give too much information than not enough. Any help/information is much appreciated.

Thanks

Gordon

 

[SkipVought]

Hi,

You will almost ALWAYS, get better answers, if you provide concrete direst examples.

I do not see your problem.

My Code Table range CodeALL
[tt]
Ax a
Bd b
Sc c
[/tt]
My Lookup
[tt]
AX a =VLOOKUP(A1,CodeALL,2,FALSE)
BD b
SC c
[/tt]
What am I missing?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ColumbiaDiver]

Skip,

Make the changes below and you should see the same issue I have, except your using a different formula, according to what I found in my research your VLOOKUP will not recognize different case.

Add the following to your code table

Ax a
AX A
Bd b
Sc c
SC C

My Lookup

AX a =VLOOKUP(A1,CodeALL,2,FALSE)
BD b
SC c

I have uploaded some screen shots to clarify to photobucket at

http://s444.photobucket.com/albums/qq168/owensct/VLOOPUP/

Thanks for the reply.

Gordon

 

[SkipVought]

How about this...

=IF(CODE(RIGHT(A1,1))>90,INDEX(OFFSET($J$1,MATCH(A1,MyCode,0),1,COUNTIF(MyCode,A1),1),1,1),INDEX(OFFSET($J$1,MATCH(A1,MyCode,0),1,COUNTIF(MyCode,A1),1),2,1))

where MyCode is ...
[tt]
MyCode
Ax a
AX A
Bd b
BD B
Sc c
SC C
[/tt]

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ColumbiaDiver]

Skip,

I tried to create a test worksheet following your example and I get a #NA which in my original formula is essentially the same as No match Found. So I'm missing something.

Can you e-mail me your example sheet? owensctAThotmail.com.

Thanks

Gordon

 

[SkipVought]

Here's a version of it that uses the CELL, INDIRECT and ADDRESS functions to remove the necessity of explicitly referencing the OFFSET anchor.
[tt]
=IF(CODE(RIGHT(A2,1))>90,INDEX(OFFSET(INDIRECT(RIGHT(CELL("filename",MyCode),LEN(CELL("filename",MyCode))-FIND("]",CELL("filename",MyCode)))&"!"&ADDRESS(ROW(MyCode)-1,COLUMN(MyCode))),MATCH(A2,MyCode,0),1,COUNTIF(MyCode,A2),1),1,1),INDEX(OFFSET(INDIRECT(RIGHT(CELL("filename",MyCode),LEN(CELL("filename",MyCode))-FIND("]",CELL("filename",MyCode)))&"!"&ADDRESS(ROW(MyCode)-1,COLUMN(MyCode))),MATCH(A2,MyCode,0),1,COUNTIF(MyCode,A2),1),2,1))
[/tt]


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[yogia]

Hi columbiaDiver:

Here is a little play I had with case sensitive exact match. With my LookupTable in cells A2:B5, my LookupValue in cell D3, then the array formula for my resulting value from column 2 of the LookupTable is ...

=MAX(IF(EXACT(A$2:A$5,D3),B$2:B$5))

see the following image for the associated illustration.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[Gavona]

Yogi: How do you insert a picture of the spreadsheet like that? Brilliant!

Gavin

 

[yogia]

Hi Gavin:

Here we go ...

1) I took a screenshot of the part of the spreadsheet that I wanted to display with a free utility such as MWsnap3

2) I saved this as a .gif file

3) I uploaded it on my website

http://www.energyefficientbuild.com

-- but it can be instead uploaded on a site such as box.net as stated on tek-tips website

4) then I used the

tag as explained in thr 'Process TGML' section of the tek-tips website

5) and then I submitted the post to the tek-tips website.

I hope this helps -- if you want to discuss this further, please post back and then let us take it from there.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi columbiaDiver:

In the simple example I posted in the preceding, I can also use the following formula (regular non-array) ...

=MAX(EXACT(A$2:A$5,D3)*B$2:B$5)



Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[ColumbiaDiver]

All,

I'm still working on this, getting closer, but not there yet. Thanks for all the excellent ideas.

I will keep all posted.

Thanks

Gordon