Getting a case sensitive match?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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))=TRUE,INDEX(A1:B5,MATCH(C1,A1:A7,0),2),"No exact match")
=IF(EXACT(C1,VLOOKUP(C1,A1:B5,1,FALSE))=TRUE,VLOOKUP(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.
 
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)
 
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!"
 
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.
 
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.
 
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)
Click to expand...

Unwise if there might be multiple matches.

There's always the array formula

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