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

 

[JonTheCells]

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

I've tried this and it works great

Can anyone explain why it works? It seems to be creating a virtual array populated with TRUE/FALSE depending on whether it finds an EXACT match, but then it INDEXes this virtual array at the 0th element? And MATCHes TRUE to some other array?

 

[RoryA]

The second INDEX merely means that you don't have to array-enter the formula with Ctrl+Shift+Enter - by specifying 0 for the row dimension, it returns the entire array of TRUE/FALSE values from the EXACT function.

 

[JonTheCells]

The second INDEX merely means that you don't have to array-enter the formula with Ctrl+Shift+Enter - by specifying 0 for the row dimension, it returns the entire array of TRUE/FALSE values from the EXACT function.

So I can either explicitly tell Excel that this is a virtual array by entering the formula with Ctrl+Shift+Enter, or I can implicitly tell it by using INDEX?

Is this a special feature of INDEX, or do other functions work in a similar way?

 

[RoryA]

It's a feature of INDEX.

 

[JonTheCells]

Thanks Rory, that's great

With your explanation I've found this
http://www.mrexcel.com/forum/excel-questions/636107-very-strange-result-index-row=0;-help.html

which has helped