Good Morning & Happy Thursday
IF,COUNTIF,INDEX,MATCH,VLOOKUP = MY WORST ENEMY!!! I've been stuck on this for days and have given up so I'm hoping someone can please review and offer help or suggestions on how I can make this work please!
My overall worksheet is almost 3000 rows. Throughout columns A and B are matches - where their difference is column C (price). I need to add a formula to column D that checks column A and B for a match and if they match then compare the prices for the match and return in column D the MIN with a "True" or "False". If there are no matches (only one entry exists) then I want it to return the price (column C) since that is the MIN as there is only one!
Here's an example. I've highlighted to show the different scenarios and I've manually typed in the MIN PRICE (column D) to show the desired outcome.
Blue highlight: A2:B2 match A3:B3 so compare C2:C3 for MIN and return value to Column D
Yellow highlight: A4:B4 match A5:B5 so compare C4:C5 for MIN. A6:B6 has no match so return "True" since it's the only item.
Pink highlight: A7:B7 no matches, A8:B8 no matches, A9:B9 no matches so return "True" on all three rows since it's the only item.
The idea here:
There's way more to this worksheet than what is shown. The price (column C) can change depending on the discount. Some of the UDAC (column B) fields are locked from formulas in other columns not seen here that does not allow discounting thus their price will never change. As discounts are added I need it to always give me the row that is the best price for the client without offering the same item twice. So example - reviewing rows 2 and 3....C3 is actually a locked price - no changes will ever occur. C2 can change based on a percent off that I already have established in the worksheet. Currently you see C2 is $100 and C3 is $50. If I apply a higher discount and C2 becomes $25 and C3 is $50....I would need D2 and D3 to recognize that D2 is now my MIN.
I really hope that makes sense!
Thanks so much in advance for reviewing & have a wonderful day
Erin
Bookmarks