Vlookup not working - data format problem

Vlookup not working - data format problem

Didn't find your answer?

When searching a list for data using lookup functions excel does not match like values because (I assume) the format of the cells is not the same. When the format has been changed to match the other format the lookup still appears as N/A. Is there a way around this problem, other than retyping?
Andrew Hepworth

Replies (5)

Please login or register to join the discussion.

avatar
By mattsmith2
28th Oct 2002 15:00

Index & Match does it for me
Since getting similar advice from Bob, all I use is the index & match method.

Thanks (0)
avatar
By AnonymousUser
28th Oct 2002 14:45

V Lookup
Simply apply an 'If' Statement to give a zero result.

EG If(isna(vlookup),0,(vlookup))

Thanks (0)
avatar
By neileg
28th Oct 2002 13:03

Mmmm...
This is a perennial problem with data handling in Excel. Formatting won't solve the problem, the data types need to be the same. The usual conflict is mixing numeric and text fields that all look like numbers.

There are a few ways of forcing text to become data. The way I use is to multiply the 'text number' by 1 to make it a real number, e.g. B2=A2*1

Thanks (0)
avatar
By AnonymousUser
28th Oct 2002 14:51

Don't use IF
Because that will simply disguise the fact that vlookup isn't finding a match that it probably should find. I think the lookup functions were simply inherited from Lotus 123. There is never any need to use them in Excel: see my answer below.

Thanks (0)
avatar
By AnonymousUser
28th Oct 2002 14:08

I think this will do it ...
This arose quite recently: see

http://www.bobfoley.co.uk/aweb_answers.htm

The one you want is lookup_unsorted_list.xls, and probably a version of the formula in column C.

You'll no doubt need to tweak it to suit your data. If you can't see how, please post up a specific example of the data you are trying to match.

INDEX and MATCH almost invariably give better results than LOOKUP and its brethren.

To convert pure numbers to strings you can use =UPPER(A1). For vice versa you can use =VALUE(A1). Why there is not simply a STRING function in Excel, I do not know.

Thanks (0)