VLOOKUP

[Cozza]

I have just tried familiarising myself with the above command from a few
different sources. Now at the point of frustration.

I have tried (a number of ways) to extract the corresponding price of a
product code from a worksheet. The formula seems straight forward enough (I
thought), but I seem to always be getting the price from the cell above the
one I require.

I am using the formula: =VLOOKUP(E24,'Price List'!A2:D137,4)

where 'Price List'!A2:D137 is the range. A = Product code, B = Manufacturing
Price, C = Service Cost & D = Total Price

Anyone have any ideas?

[Niek Otten]

see answer in other newsgroup. Please don't post to multiple newsgroups

"Cozza" <col.mi...@virgin.net> wrote in message
news:LW%W6.28937$9t5.2...@news6-win.server.ntlworld.com...

[Debra Dalgleish]

See possible answer for same posting on microsoft.public.excel.misc

[J.E. McGimpsey]

Since you're note using the fourth parameter of VLOOKUP (did you try
Help?), it appears that VLOOKUP is not finding an exact match, so it
returns the next highest value:

From Help:
> If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it
> uses the largest value that is less than or equal to lookup_value.

In article <LW%W6.28937$9t5.2...@news6-win.server.ntlworld.com>,
"Cozza" <col.mi...@virgin.net> wrote:

--
J.E. McGimpsey ar...@zptvzcfrl.pbz
ROT13 encoding, decode for real mail

[Deleep Nair]

You should try to use absolute refrences in your table array. So it should look
like: =VLOOKUP(E24,'Price List'!$A2:$D137,4, false). If you do not use absolute
refrences, the function will only look up 137 entries.

Deleep

.

[Bell]

Try using the Index function....

=Index(PriceList!$D$2:$D$137,Match(E24,'Price List'!$A$2:$A$137,0),1)

This will return an error if "E24" is not in the list getting around some
problems with
the VLookUp function.

--
steve

Cozza <col.mi...@virgin.net> wrote in message
news:LW%W6.28937$9t5.2...@news6-win.server.ntlworld.com...

[Nigel Thomas]

Whenever you try this sort of looking up it is far more important to check for
getting a non-existant number than anything else. If you fail to check for
this, then - just sometimes - you may get a wrong answer and not know it which
is very bad DP. Always wrap your lookup method around with an "IF (num
recovered, or found in table) = (number sought) THEN Proceed ELSE give error
mess and recover."
Regards,
Nigel Thomas

[David McRitchie]

Google barfs up on this thread. I've seen threads that Google
has done worse on but never really looked at them to see what
might be causing problems.

If anyone doubts the need for *unique subject titles* that describe
the problem this thread had 5 entries in it as I see it today in
Outlook Express all dated on 2001-06-17, including the first
post, before Nigel's on 2001-07-11.

According to Google when I looked at Nigel's post it was one of 47
in a thread that began 1997-04-16, possibly replying to
an old post might have something to do with Google barfing up.
http://groups.google.com/groups?as_umsgid=3B4BF4B0...@rmit.edu.au

I also notice that both Nigel and the posting he replied to did not connect
directly to the MS News servers, which might exacerbate the problem.
It is not possible for everyone to do so (firewalls, or their ISP).
Google will not show the headers so here is the path, from the headers.
Note there are eight exclamation points(!) each representing a hop to
another server.

References:
<LW%W6.28937$9t5.2...@news6-win.server.ntlworld.com>
<eGVD6QO#AHA.2184@tkmsftngp05>

Path: tkmsftngp01!newsfeed00.sul.t-online.de!t-online.de!fr.usenet-edu.net!
usenet-edu.net!news-spur1.maxwell.syr.edu!
news.maxwell.syr.edu!news1.optus.net.au!optus!vrn.edu.au!
131.170.8.18.MISMATCH!mercury.its.rmit.edu.au

The following article describes how to connect directly to the
MS Newsservers, specifically oriented to those using Outlook Express.
Outlook Express 5.0, Newsgroups
http://www.geocities.com/davemcritchie/excel/oe5nws01.htm
As previously noted not everyone can connect directly due to
firewalls.

Another thing that seems to be involved frequently when Google
barfs up is that the original poster posted same question to
multiple groups (separately in this case).

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

"Nigel Thomas" <nig...@rmit.edu.au> wrote in message news:3B4BF4B0...@rmit.edu.au...

> Whenever you try this sort of looking up it is far more important to check for

> getting a non-existant number than anything else. If you fail to check for [clipped]

[Ben]

Or you could simply add ",false" to the end of the formula, which forces
excel to look for an exact match - omitting the false means default to true,
which means find the closest match to the lookup value.

=VLOOKUP(E24,'Price List'!A2:D137,4,FALSE)

BDV

"Nigel Thomas" <nig...@rmit.edu.au> wrote in message
news:3B4BF4B0...@rmit.edu.au...