VLOOKUP

[Andre Ronda]

I have in sheet1 a list with an item-master (I/M) with products with
standard prices, another much shorter list with special prices. As the I/M
gets regularly updated, I want to replace the standard prices in a 3rd list
(which is a copy of the original I/M) with the special prices while keeping
the original as is.

The VLOOKUP function (in the price field) in my target list (the 3rd list)
looks like:

=IF(VLOOKUP(U2;prices;1;FALSE)=(VLOOKUP(U2;Specialprices;1;FALSE));VLOOKUP(U2;Specialprices;3;FALSE);
VLOOKUP(U2;prices;9;FALSE))

U2 is the reference cell (unique product nbr) in target list that certainly
exists in 'prices' and that can exist in 'specialprices'
'prices' is the named range in my original I/M (with standard price in 9th
column)
'specialprices' is the named range in my 2nd shorter list (with the special
prices in the 3rd column)

Result is that it indeed replaces all prices for matching product nbrs but
showing '#N/A" for all records where price does not have to change.

I have tried several variations but cannot get rid of '#N/A' to show the
original standard price instead. Any help is very much appreciated.
Thanks.

Rgrds,
Andre Ronda, Belgium

 

[Guest]

Taking for granted that the list of item-masters is a duplicate of your
standard prices, then we can simplify your formula to this

=IF(ISNA(VLOOKUP(U2;Specialprices;3;FALSE)), VLOOKUP(U2;prices;9;FALSE),
VLOOKUP(U2;Specialprices;3;FALSE))

This works because the specialprices VLOOKUP will return either a special
price or an #N/A error. In the event of an error, the ISNA() function is
TRUE and the IF statement pulls the standard price. If no error, then the IF
function responds with the last VLOOKUP for special prices

 

[Andre Ronda]

It worked. Great. As I'm not so familiar with the ISNA() function, I
would have never considered that. I will have now a closer look at that
function to understand what it actually does. Million thanks.

Rgrds,
Andre Ronda, Belgium

 

[Guest]

you also had some syntax problems in your formula. Please check it against
the required comas and parantasis. I am sure it will work. (like you need to
have comas, instead of semicolumns)

 

[Andre Ronda]

Thanks for your comment. My program did not produce any syntax error and
my formula worked well for matching fields but gave #N/A for the majority
that did not match. Maybe some parantasis could be at a wrong place.
As far as comas and/or semicolumns is concerned, in any of my other
functions, I always use semicolumns knowing that in all sample functions in
help files or Function Wizards, the comma was mentioned. That has probably
to do with my Belgium Dutch keyboard setting. The simplified formula from
Duke Carey did it although I was not familiar with that approach. Anyway,
thanks for your concern.

Happy Easter.

Andre

----- Original Message -----
From: "Anna" <[email protected]>
Newsgroups: microsoft.public.excel.worksheet.functions
Sent: Saturday, March 26, 2005 4:15 AM
Subject: RE: VLOOKUP