Compare Date with Date range and display description accordingly

[Vijay]

Hi Chandu,


The way you structured the Excel information on your web site is very impressive. Actually I was referred by on of my friend from Visakhapatnam. Nice to know your work on Excel. It's a one place for all Excel related problems.

Well.. I am also coming to you with a problem that is bugging me from a long time. Lets say I have 2 sheets.

Sheet 2 has all my dates and their description. Lets say in Sheet 2 Column A data is , from A1 to A10 is all date fields ( dates ranging from Jan 2011 to Dec 2011) and their description in Column B.


In Sheet 1 on Cell A1 ( or any other cell ) if I input a date field , the logic should check the dates in Sheet2 ( from Sheet2!A1 all the way to A10 ) and if it is in one of those dates I want to get it's asoociated description here in Sheet 1.


I looked at the Arrays and their implementation, but could not able to come at a solution. Any help in resolving this may be highly appreciated. If you feel that this is something it takes reasonable amount of your time please ignore my request.

 

[Hui]

Vijay

Using your example: =INDEX(Sheet2!$A$1:$B$10,MATCH(A1,Sheet2!$A$1:$A$10,0),2)


is using =Index(Area,Row No, Col No)

Index will lookup the area Sheet2!$A$1:$B$10

It then requires a Row and Column No.

Row No : MATCH(A1,Sheet2!$A$1:$A$10,0) looks up the value in A1 from the range Sheet2!$A$1:$A$10 and returns the position of your date

Column No : 2


The Table on Sheet2 doesn't have to be sorted

 

[Vijay]

Hui.. This is awesome. Very nice and works great. Thanks for your deailed explanation too. You can really do wonders with Excel.


You solved my problem. Now if for some reason the Value in A1 on Sheet does not match with the array from A1 to A10 on Sheet2 , the cell value is displaying as "#N/A". How to change this to a blank or display nothing if there is no match ?


Thanks again for your time - Vijay

 

[Vijay]

Hui, I just looked at the documentation of MATCH and INDEX and I could figured it out. Its a matter of if and else conditions and checking for ISNA function. Thanks for the direction my problem is solved...