VLOOKUP (a bit quirky)

BarrettM

Board Regular
Joined
Mar 13, 2002
Messages
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top