Excel IF lookup found then display a value, IF no match then sum

[HNarli]

Hi, please can someone help advise me. I only know basic formulas in Excel (Not macros).

I have a spreadsheet where I want to perform a lookup value to a list of products. If the product is found in the list I would like to display the product price, if no product is found then I would like it to do a sum instead. I have tried to get to the bottom of this but I just get errors each time! I will write in words what I am trying to ask for…

F5 = The space to type in the product item code

J5 = The product price

The source of the product list (Prices spreadsheet) = A:\Marketing\Templates (Do Not Move or Delete)\Sales\Quotations\[Prices_AR.xlsx] in column 1

IF the product code entered in F5 matches one in the Prices spreadsheet. THEN put the product price into cell J5 …. IF the code cannot be found in the spreadsheet THEN do the following sum: =([@[Unit Sell (unround)]]+[@[WEEE Care]])*(100+$B$2)%

Thanks so much in advance!!!

 

[jasonb75]

It looks like you need to use

=IFERROR(VLOOKUP(F5,[Prices_AR.xlsx]SheetName!Range,PriceColumn,FALSE),([@[Unit Sell (unround)]]+[@[WEEE Care]])*(100+$B$2)%)

Note that you will need to correct the section in red, there is inadequate information in your post for me to do it.

 

[HNarli]

Amazing! Thank you so much!!!!

 

[jasonb75]

The formula only has 2 options, show the lookup, or show the alt sum if there is no match, unless you've made other changes to the formula then it is impossible for it to show anything else.

If you're seeing a #VALUE! error then that would mean that 1 or more of the cells referred to in the alt sum section of the formula, ([@[Unit Sell (unround)]]+[@[WEEE Care]])*(100+$B$2)%) doesn't contain a valid number.