URGENT - How to match values in two columns then perform an action

[VBAhelp3456]

Hi all,

Sorry I putting a presentation together and require some much needed assistance


I have a range of prices in column B - a lot of them are duplicates. I need to match the value in column A of sheet5. if they match, then I would like to determine if the the values in column G are outliers or Sample.xlsxnot. The average & standard deviation are next to the code in sheet5. The below formula works for the first cell but errors thereafter;

IF(Sheet1$B$1:$B$6=Sheet5!A2,IF(OR(Sheet1!G3<Sheet5!B2-4*Sheet5!B2,Sheet5!G3>Sheet5!B2+4*Sheet5!C2),"outlier?","OK"))


I have attached a spreadsheet for your reference.

Thanks you very much!

[FDibbins]

Try this ARRAY formula, copied down...
=IFERROR(INDEX(Sheet1!$H$1:$H$14,MATCH(Sheet5!A2&"outlier",Sheet1!$B$1:$B$14&Sheet1!$H$1:$H$14,0)),"ok")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.