Vlookup

[cashmire]

How can I make a Vlookup that is not depending on wether or not the source document is in CAPITALS or normal text size? So far I have only managed to make it work by making my reference list exactly the same as my source document but I would like to change this...is this possible?

 

[dk]

How can I make a Vlookup that is not depending on wether or not the source document is in CAPITALS or normal text size? So far I have only managed to make it work by making my reference list exactly the same as my source document but I would like to change this...is this possible?

Hi,

I didn't realise VLOOKUP was case sensitive. e.g. I put dan, ben, jon in cells A1, A2 and A3 and 26, 23 and 25 in B1, B2 and B3 of sheet1. Then on sheet2 I type DAN in A1 and =VLOOKUP(A1,Sheet1!A1:B3,2,FALSE) in B2 I get the result I want - 26. However, there is obviously something I'm missing with your particular case. If your list is in capitals then use:-

=VLOOKUP(UPPER(A1),Sheet1!A1:B3,2,FALSE)

HTH
Dan

 

[Mark W.]

On 2002-04-03 04:46, cashmire wrote:
How can I make a Vlookup that is not depending on wether or not the source document is in CAPITALS or normal text size? So far I have only managed to make it work by making my reference list exactly the same as my source document but I would like to change this...is this possible?

The Excel Help topic for the VLOOKUP worksheet function states, "Uppercase and lowercase text are equivalent."

If the VLOOKUP function's 4th (optional) argument (Range_lookup) is 1, TRUE or omitted make sure that your lookup Table_array is sorted in ascending order.

If the VLOOKUP function's 4th (optional) argument (Range_lookup) is 0 or FALSE make sure that the Lookup_value that you're searching for has a matching value in the left-most column of the Table_array. By "matching value" I mean an exact match (i.e., the both must have the same number of trailing spaces if present).

 

[Chris Davison]

do you mean the filename or the range name is different case ?

UPPER / lower....