#VALUE message in cell

[Guest]

Hello. I am am looking at using a vlookup formula on a worksheet in 2007.
It is listed below. The "Rates" is the other worksheet that has the info in
to look up. I am getting the #VALUE error message in the cell, whether I do
the cntrl + shift + enter or not.

=(VLOOKUP($Z6,Rates!C4:Q16,(ROUND(((+$X6+2+AG$2)-0.5),0))+1))

Can anyone see where I am going wrong on this formula?

Thanks to anyone responding.
.... John

 

[Guest]

Your vlookup formula is not written with correct syntax...

The components of the vlookup are

=vlookup( the item being looked up , the range to look it up on with the
first column being the column to match on , the column on the range you are
looking it up on to bring back , and then true or false for an exact match or
the next highest match)

Thanks,
Ray

 

[Guest]

actually, the syntax is fine, though I would recommend putting in the last
FALSE, unless you are intentionally doing a range lookup, which is usually a
dangerous thing. It finds the closest value, without going over. The last
parameter is optional to Excel (though I think it should be required, or
defaulted to false, whereas it defaults to true.).

The syntax, as originally stated, tries to find the content of Z6 in the
range on the rates table from C4 to Q16, in the column that is determined by
the round formula. There's not syntactical problem with that, and I have
just recreated that in my spreadsheet to prove that.

But I can't seem to replicate your #VALUE . That means that it thinks the
answer should be a number, but it can't figure out the answer. All I've been
able to get the formula to evaluate to, other than a number, is either #N/A,
when I intentionally try to look up something that is not in the list in
cells C4:C16, or #REF if I intentionally slip a character on the name of
something.

But your syntax was fine, though as I said, I'd recommend explicitly stating
whether you want to use a range lookup, or not.

 

[Guest]

Ray, thanks for the quick response. The formula (per your response) would be;

item being looked up = $Z6,
the range to look it up on = Rates!C4:Q16,
column on the range to bring back = ????,
the true/false = (ROUND(((+$X6+2+AG$2)-0.5),0))+1))

The column on the range part is missing and I'm a bit lost on what to put in
there for it. Can you explain a bit more? Is it a number? Letter? Column
reference?

Thanks ... John

 

[Guest]

the column number is a numerical reference.

your lookup table is from colum C to column Q... a span of 15 columns. So,
column C is 1, column D is 2, column E is 3, etc. out to column Q being 15.

But I thought your round() function was designed to pick the column, not
whether the range lookup was TRUE or FALSE .