Involved VLookup Formula

[andiam24]

Hello,
This is actually a re-post. I didn't get an answer for the initial post and
that was a week ago.

The formula is transferring the value for the item in, for example, A8-
calculated on one of the three worksheets- to a summary worksheet. I would
like the summary worksheet to pull the "best" value- that being within the
range of 0.2 and 2.6. If this condition is not met, I would like the summary
worksheet to lookup the next best value- within the range of 0.2 and 6.0.

=IF(ISNA(VLOOKUP(A8,'NA 3'!$A$84:$A$107,1,FALSE)),IF(ISNA(VLOOKUP(A8,'NA
2'!$A$84:$A$107,1,FALSE)),INDEX(NA!$M$24:$M$47,MATCH(MAX(IF((NA!$A$24:$A$47=A8)*(NA!$K$24:$K$47>0.2)*(NA!$K$24:$K$47<6),NA!$K$24:$K$47)),NA!$K$24:$K$47,0)),INDEX('NA
2'!$M$24:$M$47,MATCH(MAX(IF(('NA 2'!$A$24:$A$47=A8)*('NA
2'!$K$24:$K$47>0.2)*('NA 2'!$K$24:$K$47<6),'NA 2'!$K$24:$K$47)),'NA
2'!$K$24:$K$47,0))),INDEX('NA 3'!$M$24:$M$47,MATCH(MAX(IF(('NA
3'!$A$24:$A$47=A8)*('NA 3'!$K$24:$K$47>0.2)*('NA 3'!$K$24:$K$47<6),'NA
3'!$K$24:$K$47)),'NA 3'!$K$24:$K$47,0)))

The formula only returns the first value that meets the criteria. I would
like for the formula to return the value for the criteria:
-lookup value is within the range >0.2 and <2.6 else
-lookup value is within the range >0.2 and <6.0

 

[Max]

Just some thoughts. If I'm not mistaken, think the MATCH part of it within
each of the 3 array index/match needs to be re-written, indicatively like
this:

MATCH(MAX(IF((Con1)*(Con2)*(Con3),Rng)),
IF((Cond1)*(Cond2)*(Cond3),Rng),0)

And the entire expression needs to be array-entered,
ie confirmed via pressing CTRL+SHIFT+ENTER
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[andiam24]

Hi Max,

Thanks for your help! I adjusted the formula a little and changed max to min
and it worked great!

 

[Max]

Good to hear. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik