Thread: Lookup letter and return number

Please reference attached file 'dummy late service report'. I am looking for the simplest, most elegant solution to the problem below.

I would like excel to lookup the value of column F of worksheet 'late service report', check that against the table in worksheet 'lookup table', and return a number value from that table into column I of 'later service report'.

I will then compare that value with value from column G (# of days open) to determine if that row is overdue, and if overdue, display the number of days it is overdue.

Thanks in advance for your help.

In I4:

=VLOOKUP(F4,'Lookup table'!$B$3:$D$12,3,FALSE)

in J4:

=MAX(0,G4-I4)

Note: You can use Vlookup in H4 also:

=VLOOKUP(F4,'Lookup table'!$B$3:$D$12,2,FALSE)