|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 19th, 2009, 08:21 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Involved VLookup Function
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$476),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$476),'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$476),'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. 
|
|
#2
May 19th, 2009, 09:17 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Involved VLookup Function
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$476),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$476),'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$476),'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.
|
|
#3
May 20th, 2009, 12:13 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Involved VLookup Function
Hello,
Thanks for your reply. 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. Hope this is more lucid. "Niek Otten" wrote: 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$476),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$476),'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$476),'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.
|
|
#4
May 20th, 2009, 12:20 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Involved VLookup Function
The formula- located on a summary worksheet- looks for the value for the item
in, say, A8, which is calculated in any of three worksheets in the same workbook. I would like the lookup function to return the "best" calculated value- that being within the range of 0.2 and 2.6. If this condition is not met I would like to return the next best value- that within the range of 0.2 and 6.0. Hope this is more lucid. And many thanks! "Niek Otten" wrote: 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$476),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$476),'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$476),'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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
