VLOOKUP (a bit quirky)

[BarrettM]

I have a table with 4 columns.
Col 1 has the numbers that I wish to search through, (This is in ascending order and is formatted as a general number).
Col 3 has text which I want to appear when the VLOOKUP returns an exact match.

I am using false

The problem is that VLOOKUP returns the answer sometimes but for some reason is not consistent.

Has anyone come across this problem before? and if so how could I resolve it?
This message was edited by barrettm on 2002-04-09 00:57

 

[SamS]

Aladins formula converts your Text (You keep referring to a General Number)to a number. It can be General or Number not both. Go back and format your lookup table as Number (just below General) and Aladin's solution should work.

 

[BarrettM]

I have formatted the 1st column to
number 0 decimal places(sorry about the confusion earlier) but I still have problems.

 

[SamS]

On 2002-04-09 04:37, BarrettM wrote:
I have formatted the 1st column to
number 0 decimal places(sorry about the confusion earlier) but I still have problems.

What did you mean by "Returns the answer sometimes but is not consistent". How are you referencing your lookup matrix/table eg is it a named range or are you using cell references. If the table is on another worksheet you can refer to the columns A:D without having to depend on where the last row of data is. There was some discussion on this topic some weeks ago and it seems that there are no overheads in using this method.

 

[BarrettM]

I am using a named range and looking up on the same sheet

 

[SamS]

Have you checked the named range covers all the data?

 

[Aladin Akyurek]

On 2002-04-09 04:16, BarrettM wrote:
On 2002-04-09 00:17, BarrettM wrote:
I have a table with 4 columns.
Col 1 has the numbers that I wish to search through, (This is in ascending order and is formatted as a general number).
Col 3 has text which I want to appear when the VLOOKUP returns an exact match.

I am using false

The problem is that VLOOKUP returns the answer sometimes but for some reason is not consistent.

Has anyone come across this problem before? and if so how could I resolve it?

The first column of your lookup table must be really number-formatted, if the lookup value that you feed to your VLOOKUP formula is a number.

Try:

=VLOOKUP(A1+0,lookup-table,3,0)

where A1 is your lookup-value. Adjust to suit. If this leads to the expected results, then the first column of your lookup-table is not numeric but text-formatted.

Aladin, Does the look upvalue have to formatted to number as I am looking up values such as
018
038
048
etc?

My 1st column is formatted to General number.

Try the following.

In a clean worksheet,

select A1:A2,
activate Format|Cells|Text,
and now
in A1 enter: 100
in A2 enter: 200

in A3 enter: 300
in A4 enter: 100

in E1 enter: 100
in E2 enter: 200
in E3 enter: 300

in F1 enter: Texas
in F2 enter: California
In F3 enter: New York

Notice that the values in A1:A2 look like numbers, but they are not: The values in A1:A2 are just text values. The values A3:A4 look like numbers and they are numbers. The values in E1:E3 look like numbers and they are numbers.

Now,

in C1 enter:

=VLOOKUP(A1,$E$1:$F$3,2,0)

copy this down till C4.

Examine the results and conclude.

PS. Reformatting A1:A2 as number will not solve anything. Select A1:A4 and apply Data|Text to Columns.

Aladin

 

[BarrettM]

Thanks for that Aladin
I tried out what you posted and I see what you mean.


I think that is my problem although I have not tried it out yet. It's very interesting how the formatting does not change data that already exists.

 

[Qroozn]

Meraid.
I had a look at the lookup table. While everything looks like like it is formatted the same in the lookup column 1 it actually isnt.
to prove this...
1.general allign the cells in graph admin! a11:a84
2. you will notice some cells are on the left and some on the right.
3. this means that the values are basically in different formats still.(or corrupted)
4. so format the cells to number/text or whatever is preferred
5. reenter the values in those cells
6 you will notice that as the value is reentreed the allignment will be continuous throughout the lookup.
7. resort the list so it is in ascending order again.
8. i tried it and it works fine now.


all the best