Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP problem
In AT I have the sum of money for the current month - AT2:AT32 In AU I have the days left in the month (sun & mon are not included, they are the weekend). In cell AW2 I have the formula: =VLOOKUP(0,AT2:AU33,2,FALSE) Basically if there is money in AT2, it will go to the next cell until it finds 0, to read from AU for the # of days left. My problem is there are a few occasions there could be no money for a day. I don't know if that made any sense, so I included an attachment. AT3 has no money for the day, while AT4 does. The formula doesn't recognize this, and AW2 shows 22, when it should show 20. Does anybody have any ideas for this? +-------------------------------------------------------------------+ |Filename: web.zip | |Download: http://www.excelforum.com/attachment.php?postid=4764 | +-------------------------------------------------------------------+ -- fastballfreddy ------------------------------------------------------------------------ fastballfreddy's Profile: http://www.excelforum.com/member.php...o&userid=33986 View this thread: http://www.excelforum.com/showthread...hreadid=542239 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP problem
Try...
=INDEX(AU2:AU33,MATCH(1,IF(ISNUMBER(AT2:AT33),IF(A T2:AT330,1)),0)+1) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article <fastballfreddy.27v570_1147720505.1553@excelforu m-nospam.com, fastballfreddy <fastballfreddy.27v570_1147720505.1553@excelforu m-nospam.com wrote: In AT I have the sum of money for the current month - AT2:AT32 In AU I have the days left in the month (sun & mon are not included, they are the weekend). In cell AW2 I have the formula: =VLOOKUP(0,AT2:AU33,2,FALSE) Basically if there is money in AT2, it will go to the next cell until it finds 0, to read from AU for the # of days left. My problem is there are a few occasions there could be no money for a day. I don't know if that made any sense, so I included an attachment. AT3 has no money for the day, while AT4 does. The formula doesn't recognize this, and AW2 shows 22, when it should show 20. Does anybody have any ideas for this? +-------------------------------------------------------------------+ |Filename: web.zip | |Download: http://www.excelforum.com/attachment.php?postid=4764 | +-------------------------------------------------------------------+ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP problem
thanks domenic, that does work for the excel example; however, if you put lets say $100 into AN3, making the total in cell AT3 $100. Your formula will recognize AT3 and return the result 21. The more I thought about it, what I need is a formula that will start the search at AT33 and move up (AT32, AT31 and so on) until it finds a # 0. Lets say it finds a value of 200 in AT18, it would then go to AU18-1, to return 10. any ideas? -- fastballfreddy ------------------------------------------------------------------------ fastballfreddy's Profile: http://www.excelforum.com/member.php...o&userid=33986 View this thread: http://www.excelforum.com/showthread...hreadid=542239 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP problem
In that case, try the following formula instead...
=INDEX(AU2:AU33,MATCH(2,1/IF(ISNUMBER(AT2:AT33),IF(AT2:AT330,1))))-1 ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , fastballfreddy wrote: thanks domenic, that does work for the excel example; however, if you put lets say $100 into AN3, making the total in cell AT3 $100. Your formula will recognize AT3 and return the result 21. The more I thought about it, what I need is a formula that will start the search at AT33 and move up (AT32, AT31 and so on) until it finds a # 0. Lets say it finds a value of 200 in AT18, it would then go to AU18-1, to return 10. any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup problem | Excel Discussion (Misc queries) | |||
Vlookup problem with Date Time | Excel Worksheet Functions | |||
vlookup Problem | Excel Discussion (Misc queries) | |||
VLOOKUP Problem | Excel Worksheet Functions | |||
VLOOKUP, OFFSET, MATCH PROBLEM, HELP? | Excel Worksheet Functions |