Need help with formula

farrissf
farrissf used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Microsoft Excel Expert
Top Expert 2014
Commented:
i used  first row of worksheet as helper row and colored it white so it becomes invisible and i did this because you had merged cells and then i put this formula in N9

=MAX(OFFSET($B$3,MATCH($N$4,$B$4:$B$44,0),MATCH($S$4,$C$1:$H$1,0),1,3))

please find attached.
ExpertsV1.xlsm

Author

Commented:
It's giving me an #NA, it has something to do with the columns on the offset.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
please use my workbook as i attached.

the reason you get N/A is becuase your lookup N4 or S4 must be empty
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
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")


2016-01-04-18_48_32-Microsoft-Excel-.png
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

Author

Commented:
That was what I needed, thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial