• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

TEXT & Vlookup help

Hi,

As per the below data, D column Batch Number and B column Date. by using the formulation in B, B1 '=TEXT(VLOOKUP(A1,A:E,2,1),"dd-mmm-yyyy") then the result is
00-Jan-1900. But my expected result is 19-Mar-15.

Note that in column D Batch Number two cells have a same value which is the lookup value in our formulation.

Can anyone guide me to get the result?

Thanks in Advance!


AB11 00-Jan-1900
Sl. No Date Product Batch Number Qty
1 19-Mar-15 Product1 AB11 10
2 19-Mar-15 Product1 AB11 10
3 21-Mar-15 Product1 AB13 10
4 22-Mar-15 Product2 AB14 10
5 23-Mar-15 Product2 AB15 10
6 24-Mar-15 Product2 AB16 10
7 25-Apr-15 Product2 AB17 10
8 26-Apr-15 Product3 AB18 10
9 27-Apr-15 Product3 AB19 10
10 28-Apr-15 Product3 AB20 10
11 29-Apr-15 Product3 AB21 10
12 10-May-15 Product4 AB22 10
13 11-May-15 Product4 AB23 10
14 12-May-15 Product4 AB24 10
15 13-May-15 Product4 AB25 10
16 14-May-15 Product4 AB26 10
17 15-May-15 Product4 AB27 10
 

Attachments

  • RM Price1.xlsx
    16.5 KB · Views: 1
Since the limitation of VLOOKUP is that it will only search from left to right,
to avoid this you insert a column just before your Date column and use the simple formula..
=VLOOKUP(A2,B3:C30,2,0) and than format the same with DD-MMM-YY Format
but if youdon't want to do this use this formula which will derive the same result
=INDEX(C5:C30,MATCH(A2,E5:E30,0))

excel sheet is attached for your better understand
 

Attachments

  • RM Price1.xlsx
    17 KB · Views: 2
Hi,
Use INDEX/MATCH:

=INDEX($B$4:$B$999,MATCH(A1,$D$4:$D$999,0))
adjust the range accordingly.

I will not prefer to use TEXT function here, use custom cell format instead.

Regards,

Edit: Didn't noticed Naresh already replied
 
Back
Top