vlookup formula

[awais]

i have two sheet (sheet1 & pak-1) like follow.

PAK-1

<TABLE style="WIDTH: 229pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=306 border=0><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" span=2 width=71><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD class=xl75 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" width=87 height=22>ITEM #</TD><TD class=xl75 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=71>PAK-1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=71></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 58pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=77></TD></TR><TR style="HEIGHT: 21.75pt; mso-height-source: userset" height=29><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 21.75pt; BACKGROUND-COLOR: transparent" height=29>DATE</TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> PACK </TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> PAID </TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> BALANCE </TD></TR><TR style="HEIGHT: 21.75pt; mso-height-source: userset" height=29><TD class=xl74 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #a5a5a5 0.5pt solid; HEIGHT: 21.75pt; BACKGROUND-COLOR: transparent" colSpan=3 height=29>

OPENING BALANCE​

</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: #e5e0ec"> - </TD></TR><TR style="HEIGHT: 21.75pt; mso-height-source: userset" height=29><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #a5a5a5 0.5pt solid; HEIGHT: 21.75pt; BACKGROUND-COLOR: transparent" height=29>31-Dec-08</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent">

483 ​

</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent">

​

</TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: #e5e0ec"> 483 </TD></TR><TR style="HEIGHT: 21.75pt; mso-height-source: userset" height=29><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #a5a5a5 0.5pt solid; HEIGHT: 21.75pt; BACKGROUND-COLOR: transparent" height=29>31-Dec-08</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent">

500 ​

</TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: #e5e0ec"> (17)</TD></TR></TBODY></TABLE>

Sheet1

<TABLE style="WIDTH: 186pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=249 border=0><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=87 height=21>ITEM #</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 68pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=91> DATE </TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=71> PAID </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #a5a5a5 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> PAK-1 </TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent">31-12-2008</TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent">

<TABLE style="WIDTH: 53pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=71 border=0><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=middle width=71 height=20>
#N/A
</TD></TR></TBODY></TABLE> ​

</TD></TR></TBODY></TABLE>

in sheet1 i want when i write date in the date column it should should show the paid amount for this i am using the following formula

=VLOOKUP($B$2,'PAK-1'!$A$5:$Z$1048576,3,FALSE)

why it shows the error please correct it.

thanks.

 

[joy_666]

Hey gimme some time

 

[joy_666]

Hey,

is it b'coz of 2 different date formats???????

 

[awais]

with the same date format it still give the same error,

please do something.

thanks.

 

[Peter_SSs]

It is hard to tell which rows/columns your data is in, but try this:

=VLOOKUP(B2+0.5,'PAK-1'!A:C,3,1)

 

[awais]

this is work perfectly but i am not able to understand the formula please can you explain it.

thanks.

 

[Peter_SSs]

this is work perfectly but i am not able to understand the formula please can you explain it.

thanks.

When the 4th argument of VLOOKUP is FALSE (or 0) as you had, VLOOKUP looks for an exact match, but will use the first one found. That is no good to you since your 'paid' amount appears to be next to the last of the two dates that are the same.

I made the assumption that your dates are in order so used the 4th argument of True (or 1). When the 4th argument is True and VLOOKUP cannot find an exact match (I ensured there was no exact match by adding the 0.5 to the date) VLOOKUP will use the largest value less than the lookup value. Hence, the second 31 Dec date is used in this case.

The other thing I changed was only putting 3 columns (instead of 26) in the lookup range.

Hope that helps.

 

[hardeep.kanwar]

When the 4th argument of VLOOKUP is FALSE (or 0) as you had, VLOOKUP looks for an exact match, but will use the first one found. That is no good to you since your 'paid' amount appears to be next to the last of the two dates that are the same.

I made the assumption that your dates are in order so used the 4th argument of True (or 1). When the 4th argument is True and VLOOKUP cannot find an exact match (I ensured there was no exact match by adding the 0.5 to the date) VLOOKUP will use the largest value less than the lookup value. Hence, the second 31 Dec date is used in this case.

The other thing I changed was only putting 3 columns (instead of 26) in the lookup range.

Hope that helps.

Awesome Peter_SSs

But if I have a name instead of Dates in AWAIS Example. Then What is the Formula.


Thanks in Advance

Hardeep kanwar

 

[Peter_SSs]

Awesome Peter_SSs

But if I have a name instead of Dates in AWAIS Example. Then What is the Formula.


Thanks in Advance

Hardeep kanwar

This sounds like a completely different scenario and you may need to start a new thread and provide details of your layout and requirements. However, purely a guess, instead of adding 0.5 to the lookup value, try adding a blank space. That is, try starting your formula something like this:

=VLOOKUP(B2&" ", ....

 

[hardeep.kanwar]

This sounds like a completely different scenario and you may need to start a new thread and provide details of your layout and requirements. However, purely a guess, instead of adding 0.5 to the lookup value, try adding a blank space. That is, try starting your formula something like this:

=VLOOKUP(B2&" ", ....

Thanks Peter_SSs

Works Perfectly