Plz help on excel formula

[Debraj]

Hi Sukanta...


You have a great quality of being BOSS.. as

* you are changing your mind in each post..

* requirement is increasing in each post..


haha ha ha .. just kidding..


Re-download.. and let us know...

=IFERROR(INDEX($G$3:$G$27,MATCH(1,(($F$3:$F$27=C3)*($G$3:$G$27<=A3)),0)),"")

Regards,

Deb

 

[sukanta]

Dear Debraj,


previous formula was great only remove the upper value (with N/A) if doesn't find lower value.


one more thing it is accepting closest value not closest lower value. Plz check once again.


latest formula is not working properly.

 

[Debraj]

Hi Sukanta Choudhury..


One More try..


Download the file again..


this time used formula as...

=IFERROR(LARGE(IF(($G$3:$G$27=C3)*($H$3:$H$27<=A3),$H$3:$H$27),1),"")

Regards,

Deb

 

[sukanta]

Dear Deb,


below formula is sutaible for my data.


=INDEX($G$3:$G$27,MATCH(MIN(IF($F$3:$F$27=C3,ABS($G$3:$G$27-A3))),IF($F$3:$F$27=C3,ABS($G$3:$G$27-A3)),0))


Plz note the above formula is returning the closest value. Plz rectify so that it returns only lower closest value.


it is also found that when lower value is not available its picking up upper value.

 

[Debraj]

Hi Sukanta..


I guess above formula is returns only lower closest value..

=IFERROR(LARGE(IF(($G$3:$G$27=C3)*($H$3:$H$27<=A3),$H$3:$H$27),1),"")

Please confirm.. which one is not as per your requirement..


Regards,

Deb

 

[sukanta]

Dear Deb,


This formula is not working at all. ctrl+shift+enter returns blank and only enter returns 1/0/1900 0:00


Again i am trying to let you understand my issue.


I want to find out the date and time (from column G) matching with column C but the value (Date and time) of Column G should be closet lower value (date and time) of Column A.


1. if there is no closest lower value (Matching to column C and A) then it should return N/A, not upper value.

2. in any case it should not return upper value than column A.


your index,match formula is 90% ok but bug is as below.


in ur formula i found

1. It returns the upper value when lower value is n/a.

2. it returns the upper value when lower value is not closest than upper value.


requesting to debug the above points.

 

[Debraj]

Hi Sukanta..


I guess UPPER value means.. Date greater than date in Column A..

and closet value is

Can you please download the below file..


And confirm.. in which cell and case its showing

* It returns the upper value when lower value is n/a.

* it returns the upper value when lower value is not closest than upper value.


https://dl.dropbox.com/u/78831150/Excel/Sukanta%20Help%20Formula%202.xlsm


Don't forget to enable MACRO..


Regards,

Deb

 

[sukanta]

Dear Deb,


its looking correct but i am unable to copy the same in my original sheet.


Plz help.

 

[Debraj]

oohhh.. thats the problem..


coz, I have added an extra blank column


In my provided file..

Select Column F;

Press (Ctrl + -)


Now copy formula in D3.. and in your Original Sheet.. select cell.. Press F2.. Paste Formula. and then press CTRL + Shift + Enter


https://dl.dropbox.com/u/78831150/Excel/Sukanta%20Help%20Formula.xlsx


Regards,

=DEC2HEX(3563)

 

[sukanta]

Dear Deb,


Thanks for your kind support. The formula is working perfectly as per my requirement.


Great……….Thank you once again …..