Can you explain under what conditions you want to lookup what where?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"andiam24" wrote in message
...
Hello,

I'm using the following formula to pull a value from one of three
worksheets
based on the criteria:
- lookup value is within the range 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,MAT CH(MAX(IF((NA!$A$24:$A$47=A8)*(NA!$K$24:$K$470.2) *(NA!$K$24:$K$47<6),NA!$K$24:$K$47)),NA!$K$24:$K$4 7,0)),INDEX('NA
2'!$M$24:$M$47,MATCH(MAX(IF(('NA 2'!$A$24:$A$47=A8)*('NA
2'!$K$24:$K$470.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$470.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

Any suggestions would be wonderful.