I am attempting to do a vlookup and I have case sensitive items i.e. a1/A1.
My data goes from A1,A2,A3,A4,A5,A6, B1...c5, c6. I want the data from the
second column when done. I have tried:
=IF(EXACT(C1,INDEX(A1:B5,MATCH(C1,A1:A7,0),1))=TRU E,INDEX(A1:B5,MATCH(C1,A1:A7,0),2),"No exact match")
=IF(EXACT(C1,VLOOKUP(C1,A1:B5,1,FALSE))=TRUE,VLOOK UP(C1,A1:B5,2,FALSE),"No
exact match")
They both find the first instance and do not find the second. They will
return the data in the second column or give "No exact match" and not find
the second instance.

Assuming the data you want to match is in A1:A5, you want to return whatever
is in B1:B5, C1 contains the criteria you want to match in column A, then:

=INDEX(B1:B5,MIN(IF(EXACT(C1,A1:A5)=FALSE,"",EXACT (C1,A1:A5)*ROW(INDIRECT("1:"&ROWS(A1:A5))))))

which must be entered with Control+Shift+Enter (it is an array formula).

Does this help?



"ob1kenob" wrote:

I am attempting to do a vlookup and I have case sensitive items i.e. a1/A1.
My data goes from A1,A2,A3,A4,A5,A6, B1...c5, c6. I want the data from the
second column when done. I have tried:
=IF(EXACT(C1,INDEX(A1:B5,MATCH(C1,A1:A7,0),1))=TRU E,INDEX(A1:B5,MATCH(C1,A1:A7,0),2),"No exact match")
=IF(EXACT(C1,VLOOKUP(C1,A1:B5,1,FALSE))=TRUE,VLOOK UP(C1,A1:B5,2,FALSE),"No
exact match")
They both find the first instance and do not find the second. They will
return the data in the second column or give "No exact match" and not find
the second instance.

I have been attempting with a simple listing enter the following data:

A1
a1 32
A1 53

Changing cell C1 from A1 to a1 I should get either 32 or 53.

=INDEX(B1:B5,MIN(IF(EXACT(C1,A1:A5)=FALSE,"",EXACT (C1,A1:A5)*ROW(INDIRECT("1:"&ROWS(A1:A5))))))

returns "#VALUE!"

Or, if your data in column B (using my previous assumptions) was numeric and
there is only one item that will match your criteria

=SUMPRODUCT(EXACT(A1:A5,C1)*B1:B5)


"ob1kenob" wrote:

I am attempting to do a vlookup and I have case sensitive items i.e. a1/A1.
My data goes from A1,A2,A3,A4,A5,A6, B1...c5, c6. I want the data from the
second column when done. I have tried:
=IF(EXACT(C1,INDEX(A1:B5,MATCH(C1,A1:A7,0),1))=TRU E,INDEX(A1:B5,MATCH(C1,A1:A7,0),2),"No exact match")
=IF(EXACT(C1,VLOOKUP(C1,A1:B5,1,FALSE))=TRUE,VLOOK UP(C1,A1:B5,2,FALSE),"No
exact match")
They both find the first instance and do not find the second. They will
return the data in the second column or give "No exact match" and not find
the second instance.

This one works. Can I make it so that it will lookup in columns A:B? I would
like to copy the cell into many in the same column. My other option is to
duplicate my array many times vertically and with 156 entries it is quite
long.
Thanks for the help.

"JMB" wrote:

Or, if your data in column B (using my previous assumptions) was numeric and
there is only one item that will match your criteria

=SUMPRODUCT(EXACT(A1:A5,C1)*B1:B5)


"ob1kenob" wrote:

I am attempting to do a vlookup and I have case sensitive items i.e. a1/A1.
My data goes from A1,A2,A3,A4,A5,A6, B1...c5, c6. I want the data from the
second column when done. I have tried:
=IF(EXACT(C1,INDEX(A1:B5,MATCH(C1,A1:A7,0),1))=TRU E,INDEX(A1:B5,MATCH(C1,A1:A7,0),2),"No exact match")
=IF(EXACT(C1,VLOOKUP(C1,A1:B5,1,FALSE))=TRUE,VLOOK UP(C1,A1:B5,2,FALSE),"No
exact match")
They both find the first instance and do not find the second. They will
return the data in the second column or give "No exact match" and not find
the second instance.

SUMPRODUCT cannot work w/an entire column, but you should be able to use
A$1:A$65535, B$1:B$65535, and C$1:C$65535

Also, w/the index function (although moot at this point), after typing it
in, you must hold Control and Shift keys while pressing Enter as it is an
array formula - I think this is usually the reason for the #VALUE error. It
also cannot accept entire columns for arguments.


"ob1kenob" wrote:

This one works. Can I make it so that it will lookup in columns A:B? I would
like to copy the cell into many in the same column. My other option is to
duplicate my array many times vertically and with 156 entries it is quite
long.
Thanks for the help.

"JMB" wrote:

Or, if your data in column B (using my previous assumptions) was numeric and
there is only one item that will match your criteria

=SUMPRODUCT(EXACT(A1:A5,C1)*B1:B5)


"ob1kenob" wrote:

I am attempting to do a vlookup and I have case sensitive items i.e. a1/A1.
My data goes from A1,A2,A3,A4,A5,A6, B1...c5, c6. I want the data from the
second column when done. I have tried:
=IF(EXACT(C1,INDEX(A1:B5,MATCH(C1,A1:A7,0),1))=TRU E,INDEX(A1:B5,MATCH(C1,A1:A7,0),2),"No exact match")
=IF(EXACT(C1,VLOOKUP(C1,A1:B5,1,FALSE))=TRUE,VLOOK UP(C1,A1:B5,2,FALSE),"No
exact match")
They both find the first instance and do not find the second. They will
return the data in the second column or give "No exact match" and not find
the second instance.

JMB wrote...
Or, if your data in column B (using my previous assumptions) was numeric and
there is only one item that will match your criteria

=SUMPRODUCT(EXACT(A1:A5,C1)*B1:B5)

Unwise if there might be multiple matches.

There's always the array formula

=INDEX(B1:B5,MATCH(TRUE,EXACT(A1:A5,C1),0))