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

lookup unique text values with criteria

S

Sandesh s

New Member
Dear friends

Need your help to easy my workload.

I have attached the example query. Plz help me to solve it..

Thanking...you in advance

Sandesh s
 

Attachments

  • Screenshot_2017-07-30-09-12-34-349_cn.wps.moffice_i18n.png
    Screenshot_2017-07-30-09-12-34-349_cn.wps.moffice_i18n.png
    206 KB · Views: 8
  • lookup for unique data with specific criteria.xlsx
    547.8 KB · Views: 3
Last edited:
Dear narayan

Thank you for the formula. But its invisible whenever i select and entered that cell. I will be very good if you please give me the formula which will not go invisible after entering the cell.

Thanking you

Sandesh s
 
Hi ,

I am not able to understand your problem ; can you please explain which cell you are referring to , and what the problem is in that cell ?

Narayan
 
Dear narayan

Whenever i go to cell no a25 Or b25press f2 cell and entered it will cell value get invisible.

plz request you to give formula in which the result will be fixed.

Sandesh s.
 
Sandesh s said:
Dear narayan

Whenever i go to cell no a25 Or b25press f2 cell and entered it will cell value get invisible.

plz request you to give formula in which the result will be fixed.

Sandesh s.
Click to expand...
Hi ,

That is happening because the formula in the cell is an array formula , which has been entered using the key combination CTRL SHIFT ENTER.

Once you press F2 to go into EDIT mode , you should either press the ESC key to exit , or press the key combination CTRL SHIFT ENTER to enter the formula once more and exit. If you press only the ENTER key , the formula will not be correctly entered , and the cell will display a blank.

Narayan
 
Dear narayan.

Its request if you could give some fixed formula. As there is lots of person who will handle this excel sheet and there is more possibility of changes in the sheet.

Sandesh s
 
Wow thanks for this formula....its working as i want.

I will be more obliged if you could help me out in adding more criteria for which i added the sample file.

Really thank you if you could help me out.

Sandesh s
 

Attachments

  • lookup for unique data with specific criteria - updated query.xlsx
    16.4 KB · Views: 2
Dear narayan thank you very much for your support.

But i have one question. Can i add more criteria for lookup the code . I.e. if i want to add the condition that it should lookup the code if the realisation date is greater or blank to the current criteria

Thank u in advance.

Also i would be more obliged if you explain how you creat the formula from your excellent mind.

thanks dear

Sandesh s
 
Dear friends.

Please help to improve the formula. Thanks to narayan for original formula.

Need your help in improving.the formula. Attached query for your references.


Sandesh shinde
 

Attachments

  • lookup for unique data with specific criteria - final query.xlsx
    16.5 KB · Views: 4
Dear narayan.

Thanks for email. In ref. result should be - lookup those id number where ever reconcilation date is blank ( earlier than bank date) and reconcilation date is greater than bank date.

Thank you in advance.

Sandesh s.
 
Dear narayan

Sorry for delay in uploading the example.

I have attached example file for your reference.

Thanks in advance.

Sandesh s
 

Attachments

  • lookup for unique data with specific criteria - final Final Query.xlsx
    17 KB · Views: 8
Hi ,

Just a clarification required ; you have written this :
Transaction of bank dated 01.01.2011 which was realised in earlier date than 01.01.2011
Click to expand...
But these are transactions realized on 01.01.2011 , whose cheques were dated earlier than 01.01.2011.

Can you clarify ?

Narayan
 
Dear it is just an example. To show the values in the specific result example date of 01st january 2011 i have create the bank dates of 30th and 31st jan 2011 in data sheet , and showed the realisation date of 01st january 2011 In some of the realisation column

Hope this will clerify your doubt.

Thanks...

Sandesh s
 
Dear narayan.

I have put the id code and figure in result sheet ( for dated 1st and 5th) manually. Please help with the formula which can lookup these data automatically. For that you can put the formula in date 7th jan 2011 in result sheet.

Thanking you

And i know you will find the formula with your expert mind.


Sandesh s
 
Hiii,.

See if is OK ?...formula OK for condition 1 ...if is it OK ...please let me know ...

Regards
Rahul shewale
 

Attachments

  • lookup for unique data with specific criteria - final Final Query.xlsx
    26.3 KB · Views: 4
You must log in or register to reply here.
Back
Top