Hello!
Once more I am in need of some Excel advice
I am retrieving data from several pivottables with a simple vlookup-formula based on specific dates. However, I have encountered some problems...
If the retrieved value is 0, I want to construct a formula that searches for the most recent (closest data point to the initial date) of available data and displays this one.
Example:
Pivot
The table below represents column A,B and C.
<TABLE style="WIDTH: 187pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=249 border=0 x:str><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 71pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=94 height=17>200812</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=71 x:num="39813">31/12/2008</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=84 x:num>0.15</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>200901</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="39844">31/01/2009</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>200902</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="39872">28/02/2009</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>0.24</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>200903</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="39903">31/03/2009</TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR></TBODY></TABLE>
Formula:
=VLOOKUP(B4,pivot1,2,FALSE)
In this case the formula will display 0. However I would want it to display 0.24. The same applies if the data in C3 would be 0. Then I would the formula to display 0.15 instead, which is the most recent data available!
I hope I made myself somewhat clear
Thanks in advance!
// Martin
Once more I am in need of some Excel advice
I am retrieving data from several pivottables with a simple vlookup-formula based on specific dates. However, I have encountered some problems...
If the retrieved value is 0, I want to construct a formula that searches for the most recent (closest data point to the initial date) of available data and displays this one.
Example:
Pivot
The table below represents column A,B and C.
<TABLE style="WIDTH: 187pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=249 border=0 x:str><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 71pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=94 height=17>200812</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=71 x:num="39813">31/12/2008</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=84 x:num>0.15</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>200901</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="39844">31/01/2009</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>200902</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="39872">28/02/2009</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>0.24</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>200903</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="39903">31/03/2009</TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR></TBODY></TABLE>
Formula:
=VLOOKUP(B4,pivot1,2,FALSE)
In this case the formula will display 0. However I would want it to display 0.24. The same applies if the data in C3 would be 0. Then I would the formula to display 0.15 instead, which is the most recent data available!
I hope I made myself somewhat clear
Thanks in advance!
// Martin