vlookup and case sensitive data

[jillst24]

<TABLE style="WIDTH: 93pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=124 border=0><COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 93pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=124 height=20>0018000000Rhi4t</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 93pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=124 height=20>0018000000Rhi4T</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 93pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=124 height=20>0018000000Rhi4u</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 93pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=124 height=20>0018000000Rhi4U</TD></TR></TBODY></TABLE>

I have data in this format. I want to do a vlookup and pull in corresponding revenue for each value but the vlookup isnt recognizing the case sensitivity.....any thoughts????

 

[Joe4]

Welcome to the board!

There are a bunch of old threads that deal with this.

Here is a formulaic solution:
http://www.mrexcel.com/forum/showthread.php?t=442533

Here is a UDF solution:
http://www.mrexcel.com/forum/showthread.php?t=399259

 

[barry houdini]

VLOOKUP isn't case sensitive

If your VLOOKUP is

=VLOOKUP(C2,A2:B10,2,0)

then this would be the equivalent but case-sensitive

=INDEX(B2:B10,MATCH(TRUE,INDEX(EXACT(C2,A2:A10),0),0))

 

[pagrender]

The PROPER Command may help you here.

When I applied the PROPER function to your four pieces of data, the first two and the second two looked alike after the case changed.

 

[jillst24]

thanks barry - it worked very easily

 

[jillst24]

VLOOKUP isn't case sensitive

If your VLOOKUP is

=VLOOKUP(C2,A2:B10,2,0)

then this would be the equivalent but case-sensitive

=INDEX(B2:B10,MATCH(TRUE,INDEX(EXACT(C2,A2:A10),0),0))
in this scenario is there a way to add a sumif functions if it is listed more than once? in this example i would like to sumif the values in Column B?

 

[pagrender]

I think a SUMIF should work for you:
=SUMIF($A$2:$A$10,C2,$B$2:$B$10)

At least it did when I tested it... let us know if it does not work for you.

Pete

 

[jillst24]

it doesnt appear the sumif is case sensitive though

 

[pagrender]

Sorry... I read that incorrectly.

Try this:
=SUMPRODUCT((EXACT(A2:A10,C2)*(B2:B10)))

I found this formula on this website:
http://www.scribd.com/doc/29299693/Excel-Sum-Product-Quick-Start-Guide

Pete

 

[jillst24]

it worked!!!!!!!!!!!!!!! very cool