Excel 2010 Compare Columns

I have an inventory spreadsheet that contains two columns that use VLOOKUP to find a discount multiplier. One column contains standard discount multipliers and the other special negotiated multipliers that only apply to certain items. Some of the items in the special negotiated multipliers contain #N/A because they only have a standard discount.

I want to add a column that will compare the two columns and take the lower multiplier but only when there is values in each column. If the special discount column contains #N/A it should show the standard discount.

How can I do that?
eseincAsked:
Who is Participating?
 
NBVCCommented:
or a regular formula...

=IF(ISNA(C2),B2,MIN(B2:C2))

where column C contains the specially negotiated, and column B the standard discounts...
0
 
Patrick MatthewsCommented:
Assuming that the first pair of standard and special factors is in B2:C2, use this array formula:

{=MIN(IF(NOT(ISNA(B2:C2)),B2:C2,""))}

Do not type in the curly braces.  Use Ctrl+Shift+Enter and not just Enter to finish it off.

(If you really need the higher value, then use MAX instead of MIN.)
0
 
Patrick MatthewsCommented:
If only one of the columns can have an #N/A error, then NB_VC's simpler construction would work well.  If either column can have an #N/A, then you may need the array formula after all.

:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.