VLOOKUP to return a sum/0 instead of #N/A?

[Steve H]

Hello, please excuse someone reasonably new to excel! My formula (for
a staff rota) checks values in a range of cells, entered as letters,
and totals the hours in the final cell. If there is no value in one of
the cells it returns #N/A, is it possible to alter the formula to add
the totals so far, or even just to show a 0?

My array, called shifts, is

A 05:30 16:30 10.00
B 09:00 18:15 8.25
C 06:30 17:00 9.50
D 07:30 17:15 8.75
E 07:30 18:15 9.75
F 08:00 18:15 9.25
G 12:00 21:30 10.00
I 07:00 17:15 9.25

and my formula, in I14, is
=VLOOKUP(D14,shifts,4,)+VLOOKUP(E14,shifts,4)+VLOOKUP(F14,shifts,4)+VLOOKUP(G14,shifts,4)+VLOOKUP(H14,shifts,4)

Many thanks if you can help!

Steve.

 

[JulieD]

Hi Steve

one way
=IF(ISNA(VLOOKUP(D14,shifts,4,0)),0,VLOOKUP(D14,shifts,4,0))+IF(ISNA(VLOOKUP(E14,shifts,4,0)),0,VLOOKUP(E14,shifts,4,0))+IF(ISNA(VLOOKUP(F14,shifts,4,0)),0,VLOOKUP(F14,shifts,4,0))+IF(ISNA(VLOOKUP(GE14,shifts,4,0)),0,VLOOKUP(G14,shifts,4,0))

Cheers
julieD


notice the use of the 4th parameter as well .. without it you're allowing an
approximate match on D14, with the 0 you're forcing an exact match.

 

[Ron Rosenfeld]

Hello, please excuse someone reasonably new to excel! My formula (for
a staff rota) checks values in a range of cells, entered as letters,
and totals the hours in the final cell. If there is no value in one of
the cells it returns #N/A, is it possible to alter the formula to add
the totals so far, or even just to show a 0?

My array, called shifts, is

A 05:30 16:30 10.00
B 09:00 18:15 8.25
C 06:30 17:00 9.50
D 07:30 17:15 8.75
E 07:30 18:15 9.75
F 08:00 18:15 9.25
G 12:00 21:30 10.00
I 07:00 17:15 9.25

and my formula, in I14, is
=VLOOKUP(D14,shifts,4,)+VLOOKUP(E14,shifts,4)+VLOOKUP(F14,shifts,4)+VLOOKUP(G14,shifts,4)+VLOOKUP(H14,shifts,4)

Many thanks if you can help!

Steve.

If "shifts" is in the range M18, the following formula will do what you want,
I think:

=SUMPRODUCT((D14:H14=M1:M8)*P18)


--ron

 

[Steve H]

Thanks Julie, haven't had time to fully explore it but it seems to work, cheers!