VBA formula to compare and select largest value
On the "Reduced_Data" sheet, I need to take an additional step that I can't work into the formula.
For ONLY column A "F1", "F2", or "F3" -
Formulas in columns U, V, W X
Net Rates 1 OLD DIM
Net Rates 2 OLD DIM
Net Rates 1 NEW DIM
Net Rates 2 NEW DIM
* Enter formulas (only in rows where column A = OR{"F1", "F2", "F3"})
* Copy down all rows each column
* Save as value
formula.xlsx
For ONLY column A "F1", "F2", or "F3" -
Formulas in columns U, V, W X
Net Rates 1 OLD DIM
=IF(VLOOKUP($S2,INDIRECT($A2&"_1"),$C2,TRUE)*$S2>VLOOKUP($S2,INDIRECT($A2&"_MIN_1"),$C2,TRUE),VLOOKUP($S2,INDIRECT($A2&"_1"),$C2,TRUE)*$S2,VLOOKUP($S2,INDIRECT($A2&"_MIN_1"),$C2,TRUE))Net Rates 2 OLD DIM
=IF(VLOOKUP($S2,INDIRECT($A2&"_2"),$C2,TRUE)*$S2>VLOOKUP($S2,INDIRECT($A2&"_MIN_2"),$C2,TRUE),VLOOKUP($S2,INDIRECT($A2&"_2"),$C2,TRUE)*$S2,VLOOKUP($S2,INDIRECT($A2&"_MIN_2"),$C2,TRUE))Net Rates 1 NEW DIM
=IF(VLOOKUP($T2,INDIRECT($A2&"_1"),$C2,TRUE)*$T2>VLOOKUP($T2,INDIRECT($A2&"_MIN_1"),$C2,TRUE),VLOOKUP($T2,INDIRECT($A2&"_1"),$C2,TRUE)*$T2,VLOOKUP($T2,INDIRECT($A2&"_MIN_1"),$C2,TRUE))Net Rates 2 NEW DIM
=IF(VLOOKUP($T2,INDIRECT($A2&"_2"),$C2,TRUE)*$T2>VLOOKUP($T2,INDIRECT($A2&"_MIN_2"),$C2,TRUE),VLOOKUP($T2,INDIRECT($A2&"_2"),$C2,TRUE)*$T2,VLOOKUP($T2,INDIRECT($A2&"_MIN_2"),$C2,TRUE))* Enter formulas (only in rows where column A = OR{"F1", "F2", "F3"})
* Copy down all rows each column
* Save as value
formula.xlsx
ASKER
@ProfessorJimJam - is this a VBA or just formulas pasted in the excel file?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i have added the requirement into the columns U, V, W X.
currently, you have REF error and that is not something caused with my formula, it is becuase you used indirect method for a reference lookup table which does not exist. i assume you know that.
formula.xlsx