Problems with VLOOKUP

[collierd]

Hello

I am using MS Excel XP and am attempting to run a vlookup and seem to be having problems

When I click on a cell as the lookup value it displays without quotes
Yet when I select cells for comparison within Table array it displays all data surrounded by quotes

The data looks identical and is formatted as text
If I click on either version it displays exactly as it is is seen within the cell, within the fx box

Data can be text or numeric and this only happens with numeric data, i.e. 10 would appear as 10 under loopup value and "10" under table Array

Has anybody got any thought please

Thanks

Damian.

 

[xlbo]

don't understand what you are saying here:

"Yet when I select cells for comparison within Table array it displays all data surrounded by quotes"

can you explain graphically ?

My thought however is to do with data types as you say this only happens with numeric data and you have formatted as text

Please post your formula as is with a sample of the data to be looked up and the data it is looking in

Also note the formatting that has been applied to both sets of cells



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[collierd]

Will try

I have a worksheet called Data - This will be looked at

That work sheet contains (amongst other things) a customer code and name:

Customer ID     Customer Name
12345           Customer 1
23456           Customer 2
A2345           Customer 3

*Note A2345 is not a typo

I have another worksheet called Support - this will look through to Data

Customer ID     Support
12345           10
23456           20
A2345           30

So now, onto support I want to add a new column called Customer Name
This will be created using a vlookup based on the Customer ID from Support to Data - Customer Name

Both Customer ID Columns are defined as Text

Demostrating via the wizard (using Support, cell C2 - where I want the name to appear)

When I click C2, then select = followed by VLOOKUP
Into lookup value, I specify A2
Into Value Array, I specify Data!A2:B4

At this point, next to the lookup box, 12345 appears without quotes
Next to Value Array I get "12345","Customer 1"

Using column 1 and false to give:
vlookup(a2,!DataA2:B4,1,false) it returns blank

If I do it the other way round, i.e. Data - Support
he quotes appear in the lookup box and value array appears as 12345,"Customer 1"


I just don't know where the quotes are coming from as the column data types look identical

Again, if I click on either worksheets' customer id cell and look at it in the fx box they both look identical (without quotes)


Do you have any further thoughts?


Thanks

Damian.

 

[GlennUK]

Entries looking identical is not the same as entries being the same. As Geoff has already stated ...

My thought however is to do with data types as you say this only happens with numeric data and you have formatted as text

Also note the formatting that has been applied to both sets of cells

You have to make sure that text entries are being searched for in a text list. If you using numeric entries then the search list has to be numeric ( and not text looking like numeric entries ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Fenrirshowl]

Index with an If and a couple of Match functions may do the trick - =Index(resultrange,if(iserror(match(value,lookuprange,0)),match(text(value,"@"),lookuprange,0),match(value,lookuprange,0))

Not the best answer unless you will have lots of formatting changes to do, but it could work.

Fen

 

[xlbo]

use these 2 formulae on your data

=ISNUMBER(A1)
=ISTEXT(A1)

These will tell you what your data types REALLY are as opposed to how they are formatted. Note - formatting does not change the data type if it is applied after data has been entered

If you have data coming up as text in one and number in another, that is where your problem is

You can either make all your data the same type or you can convert in the formula - see if either of these work for you:

vlookup(VALUE(a2),!DataA2:B4,1,false)
vlookup(TEXT(a2,"0"),!DataA2:B4,1,false)

I would suggest that given your mix of data, you should use all textual data

To convert your numbers to text, you can either use VBA or a formula eg:

=TEXT(A2,"0")

this will convert a number to text

once done, you may then use copy/paste special > values over your original data to convert it all to text

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question