PDA

View Full Version : Solved: VLOOKUP with capital text!!



marshybid
03-04-2008, 03:45 PM
Hi All,

I guess there are probably a number of ways of fixing this problem, I'm hoping someone will be able to help me with the best one.

I have 2 lists of names in one list is written as expected e.g. Joe Bloggs the other is written as JOE BLOGGS.

Whenever I try to use E2=IF(ISERROR(VLOOKUP(A2,B:B,1,FALSE)),"NO","YES") The vlookup doesn't find anything because one list is in capitals.

I need a solution, either specify in the formula to ignore case or capitalise the original data as a macro.

I have attached a small sample .xls

Any help/suggestions greatfully recieved :yes

Thanks,

Marshybid

Simon Lloyd
03-04-2008, 03:59 PM
Your formula was wrong....excel can perform the function

=IF(ISERROR(VLOOKUP(A2,C:C,1,FALSE)),"",VLOOKUP(A2,C:C,1,FALSE))the range is set as per your test sheet

Bob Phillips
03-04-2008, 04:04 PM
Why are you using VLLOKUP on a 1-column array, use MATCH.

marshybid
03-04-2008, 04:12 PM
Thanks all.

Works fine now.

Marshybid :bow: