asked on 1/4/2016

Need help with formula

Not sure which direction to go on this, I need to look at three conditions and return a value based on the highest values in two data sets. The first value is a geographical reference called AOR_LST, the second condition is either Lot or CRML, under each of these are three values, LTR1, LTR2, and LTR3, under CRML are three values, CRMLR1, CRMLR2,CRMLR3. So I am trying to look up the max if the criteria is given.

I know the naming convention could have been easier but I used all the easy names in other parts of the project.
ExpertsV1.xlsm

Microsoft Excel

Last Comment

farrissf

1/4/2016

ASKER CERTIFIED SOLUTION

Professor J

1/4/2016

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

farrissf

1/4/2016

ASKER

It's giving me an #NA, it has something to do with the columns on the offset.

Professor J

1/4/2016

please use my workbook as i attached.

the reason you get N/A is becuase your lookup N4 or S4 must be empty

Professor J

1/4/2016

to adapt this on your own worksheet and to take care of error.

please use this formula as also embeeded in the attached workbook

=IFERROR(MAX(OFFSET($B$3,MATCH($N$4,$B$4:$B$44,0),MATCH($S$4,$C$1:$H$1,0),1,3))," N4 and S4 must be empty")

plz see the green rectangle those cells must not left empty

also plz seee the red rectangle those are the helper cells i used and i changed the color to white so they are invisible.
ExpertsV1--2-.xlsm

farrissf

1/4/2016

ASKER

That was what I needed, thank you!