VLookup is not playing nice

[sprlvlyldy]

Hi All,

I am trying to do a simple V lookup and I am getting an #N/A returned with an error that says 'a value is not available to the formula or function.'

So I go into calculation steps and step in. It says the cell currently beiing evaluated contains a constant. I have no idea what that means so I have been surfing the web for the past couple hours and nothing I find seems to work. They are in the General Format. I know that the numbers I am looking up are in both lists.

Does anyone have any ideas how to correct this?


Thank you in advance for your time =)

 

[SirJB7]

Hi, sprlvlyldy!


And the vowels? Joke, but there aren't unless you count the "y"...


The syntax for VLOOKUP function is:

=VLOOKUP(search_argument, search_table, column_reference [, sorted_or_no])


Search argument may be a constant or value (15 or "Day"), a cell reference (A1), a formula (=A1+1), a named range for one cell only (ArgumentForSearch, being defined in named ranges) or anything that retrieves a single value (not a range A1:A2).


Search table may be a range (B1:B10, B:B), a named range (TableForSearch), or anything that retrieves a range of X by Y cells, with 1<=X<=1048576 (or 65536 for 2003 version or earliers) and 1<=Y<=16384 (or 256 for those).


Column reference may be a number or expression ranging from 1 to Y.


Order or not, optional, indicates whether the search table is ordered by column reference number or not.


These are the general guidelines that you'll find in Excel help too.


So, I suggest you to check this:

a) if you use OrderOrNot as true or 1, check if the table is ordered by related column reference column number

b) check manually that the search argument exists in proper case, spacing and length in the column reference column number


Otherwise, post here the formula and the ranged name definitions if you use them. Or consider uploading a sample file (refer to the three green sticky posts at this forums main page for instructions).


Regards!