Combination of OFFSET & VLOOKUP formula

**Polymnia** · 08-25-2011, 11:11 AM

Dear All,

Although I'm not an xls expert, I managed so far to solve my own problems except the following:

I have an excel file (see attached sample), where in column B there are multiple repeating names and in column E there is a respective date for each one. What I’m looking is a formula which will do the following calculations:
1) From column B find last time “REN” is written.
2) Differentiate MS and TS from column D. Find last “REN” for TS only.
3) Check if the corresponding date of point 1 in column E is overdue. (Date + 6 months is greater than today).
4) Show somehow overdue items.

For example:
Row 4 shows that “REN” is overdue because in column E 2/01/2011 is 2/1/2011>today (over 6 months) for TS. Whereas, row 22 shows that “REN” is not overdue because 20/5/11 <today (less than 6 months) for MS.

I will very much appreciate any idea/help from an expert!!

Thanks a lot in advance.

Poly

**NBVC** · 08-25-2011, 11:44 AM

How about applying conditional format rule to A2:G45 with formula:

=AND($B2="REN",$D2="TS",$E2<=TODAY()-180)

format with Fill or Font tab...

**Polymnia** · 08-26-2011, 04:41 AM

Thanks a lot for your time and effort NBVC!

But, I'm afraid it doesn't work. Imagine that this is a sample xls. In fact there are many more columns and up to 1,000 rows, where a formula is supposed to go and lookup to find the last inserted name. I believe either an array formula or a combination of offset and vlookup would work, but I can't figure out just yet the syntax.

Could you please try one more time?

Thanks again xls genius!

**NBVC** · 08-26-2011, 07:54 AM

Try this one then after selecting the range A2:G45 and invoking conditional formatting.

=AND($B2="REN",$D2="TS",ROW()=MATCH(2,1/(($B$2:$B$45="REN")*($D$2:$D$45="TS")))+1,$E2<=TODAY()-180)

adjust ranges to suit actual range size.

**Polymnia** · 08-30-2011, 05:17 AM

Sorry for the late reply and sorry for being vague, but my dear NBVC this is only for one text value, in this case "REN". I need to apply for all duplicate text values inserted in visitors column. That's why I'm asking a combination with vlookup or match or index...I don't know. I'm so confused!

**NBVC** · 08-30-2011, 07:41 AM

So you want all the Latest dated TS values for each text Name?

If this can be done, they will all share the same colouring.....

**Polymnia** · 08-30-2011, 08:16 AM

Dear NBVC,

Can I please send you the whole working sheet with p.m.? I know this is not used to but there is no other way to be more accurate.

In fact I need different values for MS and different for TS but taking under consideration only the last inserted text value (name).

Sorry for the trouble.

**NBVC** · 08-30-2011, 08:48 AM

You can attach the workbook within the forum.

When you reply you will see a paperclip icon in the reply box... use that to browse and upload your .xls or .xlsx file.