Vlookup Database

[gilbert]

Hi,

During the using of Vlookup, can we let Excel to be case sensitive? In
another word, if I have a database reference of A01 as Apple and a01 as
Apricot, I want Excel to return Apple when A01 is keyed in and Apricot
when a01 is keyed in.

Please advise.

 

[Peo Sjoblom]

One way

=INDEX('Sheet2'!B2:B100,MATCH(TRUE,ISNUMBER(FIND(A2,'Sheet2'!A2:A100)),0))

entered with ctrl + shift & enter

where A2 is the lookup value and Sheet2 A2:B100 the lookup table, vlookup
won't work but a regular vlookup
would look like

=VLOOKUP(A2,'Sheet2'!A2:B100,2,0)

 

[gilbert]

Hi Peo,

where shld I key in this formula? in the database reference number o
the whole database?

Why do we need to enter with ctrl + shift & enter, what's the reaso
for this?

Please elaborate.

Thank you.

Rgds,
Gilber

 

[Peo Sjoblom]

You should put this formula (adapted to your data) wherever you would have
put
the vlookup (it replaces vlookup) Just for simplicity name your table
MyTable and let's say
you would want to lookup a value in column 2 in vlookup

=VLOOKUP(Lookup_Value,MyTable,2,0)

is what it would look like, my formual adapted to a 2 column table named
MyTable would look like

=INDEX(MyTable,MATCH(TRUE,ISNUMBER(FIND(Lookup_Value,INDEX(MyTable,,1))),0),
2)

it's an array formula and has to be entered with ctrl + shift & enter

http://www.cpearson.com/excel/array.htm