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" wrote:

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;Special prices;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

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" wrote:

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;Special prices;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