| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check all three cells are greater than 0% and then lookup v
I have a spreadsheet which references 8 others and has data loaded from
another system. I am trying to get the final part of the worksheet to check the three fields (which are IF/VLOOKUPS) are ALL greater than 0%. If they are then the function is to do one final VLOOKUP to return another %. I have no problems with using IF or VLOOKUP and I have tried AND which always produces a TRUE response even if one or two of the cells are empty or zero. I can not see how LOOKUP would work and I would need more that 7 IF statements trying that route. Your help would be appreciated as this will save one person manually calculating figures for over 1/2 day in HR. Thanks in advance -- Len |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to check all three cells are greater than 0% and then lookup v
Len,
use MIN =IF(MIN(A1:A3)0,"do this lookup","Do something else") Mike "Len" wrote: I have a spreadsheet which references 8 others and has data loaded from another system. I am trying to get the final part of the worksheet to check the three fields (which are IF/VLOOKUPS) are ALL greater than 0%. If they are then the function is to do one final VLOOKUP to return another %. I have no problems with using IF or VLOOKUP and I have tried AND which always produces a TRUE response even if one or two of the cells are empty or zero. I can not see how LOOKUP would work and I would need more that 7 IF statements trying that route. Your help would be appreciated as this will save one person manually calculating figures for over 1/2 day in HR. Thanks in advance -- Len |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to check all three cells are greater than 0% and then look
Mike H
Thank you for your reply. However it returns the VLOOKUP when one of the cells is zero. Re-reading my original note I may have caused confusion so I will try again: Each of the cells must be greater than 0% before I pull through the VLOOKUP. A1 B1 C1 0% 5% 7% False no VLOOKUP 3% 5% 7% True perform VLOOKUP Hope this helps you? Thanks in advance -- Len "Mike H" wrote: Len, use MIN =IF(MIN(A1:A3)0,"do this lookup","Do something else") Mike "Len" wrote: I have a spreadsheet which references 8 others and has data loaded from another system. I am trying to get the final part of the worksheet to check the three fields (which are IF/VLOOKUPS) are ALL greater than 0%. If they are then the function is to do one final VLOOKUP to return another %. I have no problems with using IF or VLOOKUP and I have tried AND which always produces a TRUE response even if one or two of the cells are empty or zero. I can not see how LOOKUP would work and I would need more that 7 IF statements trying that route. Your help would be appreciated as this will save one person manually calculating figures for over 1/2 day in HR. Thanks in advance -- Len |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to check all three cells are greater than 0% and then look
Surely you just need:-
=IF(AND(A10,B10,C10),VLOOKUP(etc),"") -- Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "Len" wrote: Mike H Thank you for your reply. However it returns the VLOOKUP when one of the cells is zero. Re-reading my original note I may have caused confusion so I will try again: Each of the cells must be greater than 0% before I pull through the VLOOKUP. A1 B1 C1 0% 5% 7% False no VLOOKUP 3% 5% 7% True perform VLOOKUP Hope this helps you? Thanks in advance -- Len "Mike H" wrote: Len, use MIN =IF(MIN(A1:A3)0,"do this lookup","Do something else") Mike "Len" wrote: I have a spreadsheet which references 8 others and has data loaded from another system. I am trying to get the final part of the worksheet to check the three fields (which are IF/VLOOKUPS) are ALL greater than 0%. If they are then the function is to do one final VLOOKUP to return another %. I have no problems with using IF or VLOOKUP and I have tried AND which always produces a TRUE response even if one or two of the cells are empty or zero. I can not see how LOOKUP would work and I would need more that 7 IF statements trying that route. Your help would be appreciated as this will save one person manually calculating figures for over 1/2 day in HR. Thanks in advance -- Len |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to check all three cells are greater than 0% and then look
len,
That's what my formula did. here it is again with the ranges corrected. =IF(MIN(A1:C1)0,"do this lookup","Don't do lookup") Mike "Len" wrote: Mike H Thank you for your reply. However it returns the VLOOKUP when one of the cells is zero. Re-reading my original note I may have caused confusion so I will try again: Each of the cells must be greater than 0% before I pull through the VLOOKUP. A1 B1 C1 0% 5% 7% False no VLOOKUP 3% 5% 7% True perform VLOOKUP Hope this helps you? Thanks in advance -- Len "Mike H" wrote: Len, use MIN =IF(MIN(A1:A3)0,"do this lookup","Do something else") Mike "Len" wrote: I have a spreadsheet which references 8 others and has data loaded from another system. I am trying to get the final part of the worksheet to check the three fields (which are IF/VLOOKUPS) are ALL greater than 0%. If they are then the function is to do one final VLOOKUP to return another %. I have no problems with using IF or VLOOKUP and I have tried AND which always produces a TRUE response even if one or two of the cells are empty or zero. I can not see how LOOKUP would work and I would need more that 7 IF statements trying that route. Your help would be appreciated as this will save one person manually calculating figures for over 1/2 day in HR. Thanks in advance -- Len |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to check all three cells are greater than 0% and then look
Alan/Mike
Thank you for your help but I had tried both of your suggestions and it still picks up the VLOOKUP even if one or two cells are blank or Zero. Could it be the Excel settings? -- Len "Alan Moseley" wrote: Surely you just need:- =IF(AND(A10,B10,C10),VLOOKUP(etc),"") -- Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "Len" wrote: Mike H Thank you for your reply. However it returns the VLOOKUP when one of the cells is zero. Re-reading my original note I may have caused confusion so I will try again: Each of the cells must be greater than 0% before I pull through the VLOOKUP. A1 B1 C1 0% 5% 7% False no VLOOKUP 3% 5% 7% True perform VLOOKUP Hope this helps you? Thanks in advance -- Len "Mike H" wrote: Len, use MIN =IF(MIN(A1:A3)0,"do this lookup","Do something else") Mike "Len" wrote: I have a spreadsheet which references 8 others and has data loaded from another system. I am trying to get the final part of the worksheet to check the three fields (which are IF/VLOOKUPS) are ALL greater than 0%. If they are then the function is to do one final VLOOKUP to return another %. I have no problems with using IF or VLOOKUP and I have tried AND which always produces a TRUE response even if one or two of the cells are empty or zero. I can not see how LOOKUP would work and I would need more that 7 IF statements trying that route. Your help would be appreciated as this will save one person manually calculating figures for over 1/2 day in HR. Thanks in advance -- Len |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to check all three cells are greater than 0% and then look
Oh yes, I see what you mean. What about:-
=IF(AND(MIN(A1:C1)0,NOT(ISBLANK(A1)),NOT(ISBLANK( B1)),NOT(ISBLANK(C1))),"Yes","No") -- Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "Len" wrote: Alan/Mike Thank you for your help but I had tried both of your suggestions and it still picks up the VLOOKUP even if one or two cells are blank or Zero. Could it be the Excel settings? -- Len "Alan Moseley" wrote: Surely you just need:- =IF(AND(A10,B10,C10),VLOOKUP(etc),"") -- Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "Len" wrote: Mike H Thank you for your reply. However it returns the VLOOKUP when one of the cells is zero. Re-reading my original note I may have caused confusion so I will try again: Each of the cells must be greater than 0% before I pull through the VLOOKUP. A1 B1 C1 0% 5% 7% False no VLOOKUP 3% 5% 7% True perform VLOOKUP Hope this helps you? Thanks in advance -- Len "Mike H" wrote: Len, use MIN =IF(MIN(A1:A3)0,"do this lookup","Do something else") Mike "Len" wrote: I have a spreadsheet which references 8 others and has data loaded from another system. I am trying to get the final part of the worksheet to check the three fields (which are IF/VLOOKUPS) are ALL greater than 0%. If they are then the function is to do one final VLOOKUP to return another %. I have no problems with using IF or VLOOKUP and I have tried AND which always produces a TRUE response even if one or two of the cells are empty or zero. I can not see how LOOKUP would work and I would need more that 7 IF statements trying that route. Your help would be appreciated as this will save one person manually calculating figures for over 1/2 day in HR. Thanks in advance -- Len |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to check all three cells are greater than 0% and then look
Mike
Thank you for your help but I had tried your suggestion and it still picks up the VLOOKUP even if one or two cells are blank or Zero. Could it be the Excel settings? -- -- Len "Mike H" wrote: len, That's what my formula did. here it is again with the ranges corrected. =IF(MIN(A1:C1)0,"do this lookup","Don't do lookup") Mike "Len" wrote: Mike H Thank you for your reply. However it returns the VLOOKUP when one of the cells is zero. Re-reading my original note I may have caused confusion so I will try again: Each of the cells must be greater than 0% before I pull through the VLOOKUP. A1 B1 C1 0% 5% 7% False no VLOOKUP 3% 5% 7% True perform VLOOKUP Hope this helps you? Thanks in advance -- Len "Mike H" wrote: Len, use MIN =IF(MIN(A1:A3)0,"do this lookup","Do something else") Mike "Len" wrote: I have a spreadsheet which references 8 others and has data loaded from another system. I am trying to get the final part of the worksheet to check the three fields (which are IF/VLOOKUPS) are ALL greater than 0%. If they are then the function is to do one final VLOOKUP to return another %. I have no problems with using IF or VLOOKUP and I have tried AND which always produces a TRUE response even if one or two of the cells are empty or zero. I can not see how LOOKUP would work and I would need more that 7 IF statements trying that route. Your help would be appreciated as this will save one person manually calculating figures for over 1/2 day in HR. Thanks in advance -- Len |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup match greater than lookup value | Excel Worksheet Functions | |||
| Check if 2 cells match on another sheet then lookup value | Excel Worksheet Functions | |||
| Lookup "greater than or equal to" in lookup array | New Users to Excel | |||
| lookup finding the next value that is GREATER | Excel Worksheet Functions | |||
| vlookup - finding the next value that is GREATER than the lookup value? | Excel Worksheet Functions | |||
Linear Mode
