Formula problem

[kennmcginis]

Trying to avoid manually entering data.

On Sheet 1, I need to return text to a cell from one of four other sheets within the workbook. Problem is that the text to be returned is conditional based upon three other columns of data. Basically what I need help with is writing a formula that will do the following - If A1 = "Janitor" AND A2 = "123 Main St." take the DATE from A3 and go to JanitorSheet, 123 Main St. RANGE (e.g. rows 12-16, where each row represents a start date of January 1, 20xx) and find where A3 DATE is closest <= and call the cell to its right back to Sheet 1.

Hope that makes sense. Can really use an assist on this one.

[Bob Phillips]

Try

=IF(AND(A1="Janitor",A2="123 Main St."),INDEX(JanitorSheet!$B$11:$B$26,MATCH(A3,JanitorSheet!$A$11:$A$26)),"")

[kennmcginis]

xld

I got the #REF! error. I think I was unclear regarding date in A3. DATE in A3 is not equal to DATE in JanitorSheet. A3 DATE will be the closest date which is > the date on JanitorSheet. E.g. A3 DATE = June 30, 2000 should return data from row with January 1, 2000.

If you can help...

Thanks

[Aussiebear]

xld

I got the #REF! error. I think I was unclear regarding date in A3. DATE in A3 is not equal to DATE in JanitorSheet. A3 DATE will be the closest date which is > the date on JanitorSheet. E.g. A3 DATE = June 30, 2000 should return data from row with January 1, 2000.

If you can help...

Thanks

January 1, 2000 is less than June 30, 2000. Have another think about your question and then repost.

[kennmcginis]

January 1, 2000 is less than June 30, 2000. Have another think about your question and then repost.

I'm sorry, but I am correct. If you're confused, don't post a critique about my query. Find one more suitable to your skill level. I've come here looking for help, not criticism.

[Bob Phillips]

Whoa!. That is not the way to win friends and get help mate!

[kennmcginis]

If there's a part of my query that's not clear, then ask me a question. Telling me to rethink a query I've struggled with for a few days, and to repost will turn away any other possible problem solvers, which defeats the purpose of this enterprise.

However, in the spirit of it all, I've reposted my query with more specific delineations.

[Aussiebear]

If there's a part of my query that's not clear, then ask me a question. Telling me to rethink a query I've struggled with for a few days, and to repost will turn away any other possible problem solvers, which defeats the purpose of this enterprise.

However, in the spirit of it all, I've reposted my query with more specific delineations.

I asked you to rethink your post, because it was unclear from a logical point of view. I direct your attention to post#3 where you said "I got the #REF! error. I think I was unclear regarding date in A3. DATE in A3 is not equal to DATE in JanitorSheet. A3 DATE will be the closest date which is > the date on JanitorSheet. E.g. A3 DATE = June 30, 2000 should return data from row with January 1, 2000"

Excel stores dates as a number, which means that January 1, 2000 is less than June 30, 2000. Perhaps you meant an "earlier" date rather than using the > symbol.

And as an aside note, lose the attitude, if you wish to gain further asistance from this forum.