• 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.

Multiple Value Look up

N

nishad

New Member
Dear All

Here i am uploading a file that contains some products , amounts, and the date of purchase please help me

how can i find the all dates of purchase a particular item . With v look up , is that possible ? when i try with v look up i get only date one time. for eg : if i give value apple i want get result all the dates that purchase apple

Your immediate reply would be appreciable.

Thanks and Regards
Nishad. K.M
 

Attachments

  • v look up.xls
    21.5 KB · Views: 9
Just turn your data into a table and filter on any thing you want, see upload
 

Attachments

  • v look up.xlsx
    17.5 KB · Views: 4
Put in G2:

=IF(ROWS(G$2:G2)>COUNTIF($A$2:$A$39,$F$2),"",INDEX($D$2:$D$39,SMALL(INDEX(($A$2:$A$39=$F$2)*(ROW($A$2:$A$39)-ROW($A$2)+1),),COUNTIF($A$2:$A$39,"<>"&$F$2)+ROWS(A$1:A1))))

F2 is your fruit criteria

and copied down and please see the file
 

Attachments

  • v look up.xls
    33.5 KB · Views: 8
Hi Nishad,

Another option can be below array formula.

=IF(ROWS(H$3:H3)<=COUNTIF($A$2:$A$39,$G$2),INDEX($D$2:$D$39,SMALL(IF($G$2=$A$2:$A$39,ROW($A$2:$A$39)-ROW($A$2)+1),ROWS(H$3:H3))),"")

In G2 put your product name, enter with Ctrl+Shift+ENter and not just Enter.

Regards,
 
Somendra Misra said:
Hi Nishad,

Another option can be below array formula.

=IF(ROWS(H$3:H3)<=COUNTIF($A$2:$A$39,$G$2),INDEX($D$2:$D$39,SMALL(IF($G$2=$A$2:$A$39,ROW($A$2:$A$39)-ROW($A$2)+1),ROWS(H$3:H3))),"")

In G2 put your product name, enter with Ctrl+Shift+ENter and not just Enter.

Regards,
Click to expand...
 
azumi said:
Put in G2:

=IF(ROWS(G$2:G2)>COUNTIF($A$2:$A$39,$F$2),"",INDEX($D$2:$D$39,SMALL(INDEX(($A$2:$A$39=$F$2)*(ROW($A$2:$A$39)-ROW($A$2)+1),),COUNTIF($A$2:$A$39,"<>"&$F$2)+ROWS(A$1:A1))))

F2 is your fruit criteria

and copied down and please see the file
Click to expand...
 
Well, I really not understood you query, but still see the attached file.

Regards,
 

Attachments

  • v_look_up(1).xls
    30.5 KB · Views: 3
dear all i am uploading a file that i all ready get solution with using if,count if and index, let me know in that table can we figure out the solution with using conditional formatting function

regards..
Nishad K.M
 

Attachments

  • v look up.xls
    33.5 KB · Views: 3
ok sorry for the inconvenience i will explain detailed

there are four criteria in uploaded file, i want result any particular item and its date
for eg: i want to know which dates apple purchased

Apple
Apple
Apple
Apple
Apple
Apple
Apple
16-01-2012
16-01-2012
24-01-2012
02-02-2012
21-02-2012
23-02-2012
29-02-2012


this will be the answer , i am trying the solution with using conditional formatting
 
What fo you mean by " i am trying the solution with using conditional formatting"?

CF is used to highlight cells. Can you upload a sample file with the desired output results.

Regards,
 
in the attached file high light with yellow that's the result, a particular product purchased on different date,
 

Attachments

  • v look up.xls
    33.5 KB · Views: 2
  • v look up.xls
    33.5 KB · Views: 1
Nishad,

Sorry to inform you, I still did not got your point. You intial post was to get various dates of purchase when product match. The file has that solution.

Regards,
 
You must log in or register to reply here.
Back
Top