VLOOKUP (a bit quirky)

BarrettM · Apr 9th, 2002, 01:17 AM

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 ]

Qroozn · Apr 9th, 2002, 01:22 AM

make sure the last bit of the vlookup statement is set to FALSE

Qroozn · Apr 9th, 2002, 01:24 AM

the FALSe statemnt is normally what gives the error.
also make sure the loojup covers all the relevant cells, and that formatting is consistent.
and make sure the 1st column is sorted correctly and there should be no problems at all.

Aladin Akyurek · Apr 9th, 2002, 02:00 AM

Quote:

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?

[ This Message was edited by: barrettm on 2002-04-09 00:57 ]

I don't know what happened to the reply that I wanted to post!

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.

[ This Message was edited by: Aladin Akyurek on 2002-04-09 01:17 ]

Qroozn · Apr 9th, 2002, 02:05 AM

ok.
is the lookup only in one cell or multiple cells?

are the errors only occuring in ceratin lookups,

are the lookups all the same lookup matrix?

BarrettM · Apr 9th, 2002, 02:08 AM

Quote:

On 2002-04-09 01:05, Qroozn wrote:
ok.
is the lookup only in one cell or multiple cells?

are the errors only occuring in ceratin lookups,

are the lookups all the same lookup matrix?

The lookup formula is in one cell.
The errors are occuring for particular lookups.
I don't understand what you mean by lookup matrix.

Qroozn · Apr 9th, 2002, 02:11 AM

lookup matrix is the area of the lookups. but is not the problem if you only have one lookup.

can you send me the sheet or not?

BarrettM · Apr 9th, 2002, 02:12 AM

I can send you the workbook, it was created in excel 97.

Qroozn · Apr 9th, 2002, 02:14 AM

ok. send it to me.
i do alot of work with lookups
and have 97 running.

BarrettM · Apr 9th, 2002, 05:16 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 01:17 AM	#1
BarrettM Board Regular Join Date: Mar 2002 Location: Northern Ireland Posts: 113	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 ]

Apr 9th, 2002, 01:22 AM	#2
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	make sure the last bit of the vlookup statement is set to FALSE

Apr 9th, 2002, 01:24 AM	#3
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	the FALSe statemnt is normally what gives the error. also make sure the loojup covers all the relevant cells, and that formatting is consistent. and make sure the 1st column is sorted correctly and there should be no problems at all.

Apr 9th, 2002, 02:05 AM	#5
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	ok. is the lookup only in one cell or multiple cells? are the errors only occuring in ceratin lookups, are the lookups all the same lookup matrix?

Apr 9th, 2002, 02:11 AM	#7
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	lookup matrix is the area of the lookups. but is not the problem if you only have one lookup. can you send me the sheet or not?

Apr 9th, 2002, 02:12 AM	#8
BarrettM Board Regular Join Date: Mar 2002 Location: Northern Ireland Posts: 113	I can send you the workbook, it was created in excel 97.

Apr 9th, 2002, 02:14 AM	#9
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	ok. send it to me. i do alot of work with lookups and have 97 running.

Apr 9th, 2002, 05:16 AM	#10
BarrettM Board Regular Join Date: Mar 2002 Location: Northern Ireland Posts: 113	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.