What I am trying to do is I have a shift resource of 17 weeks Monday -
Sunday, and a review to quick glance and see what people are working in that
week, so when I input a person into a cell for 15:00 - 23:00 on a Monday it
will report 15:00 - 23:00 in the cell under Monday, this has to be variable
as other cells to lookup have other shift times, but I need the correct shift
time to report in the quick glance review sheet as this is needed to total
the number of shifts each person works in a week.

Can you assist me with some help?

Regards

Mark

"Patrick Molloy" wrote:

good grief! you've gone from looking up two columns to multiple columns it
seems!

nested IFs then become totally unwieldy. Write a UDF instead

generally a VLOOKUP returns an error if the item being looked for doesn't
exist
so "most" sheet formula are written as Bernard showed

NOT
=VLOOKUP(what, where, columnA, false)
but to check for the error , in your case, lookup something else

= IF ( ISERROR (vlookup1) , vlookup2, vlookup1)

and one more error check

= IF ( ISERROR (vlookup1) , IF (iserror (vlookup2) , "NEITHER", vlookup2) ,
vlookup1)
(Bernard's formula below)

what exactly are you trying to achieve?




"terilad" wrote in message
...
Can you have a look at the following code, I am trying to enter but
getting
an error with formula

=IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),VLOOKUP('Kelso
Resources'!E10,C25:G25,5,FALSE)),VLOOKUP('Kelso
Resources'!D14,C25:G25,3,false)),VLOOKUP('Kelso
Resources'!D18,C25:G25,4,false)),VLOOKUP('Kelso
Resources'!D24,C25:G25,3.false)),VLOOKUP('Kelso
Resources'!D28,C25:G25,2,false)),VLOOKUP('Kelso
Resources'!E30,C25:G25,5,false)),VLOOKUP('Kelso
Resources'!D34,C25:G25,4,false)),VLOOKUP('Kelso
Resources'!D8,C25:G25,2,FALSE)

Many thanks


Mark

"Bernard Liengme" wrote:

Untested - please double check parentheses:
=IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),VLOOKUP('Kelso
Resources'!E10,C25:G25,5,FALSE), VLOOKUP('Kelso
Resources'!D8,C25:G25,2,FALSE)))

If the D8 lookup fails, use the E10 lookup

If both fail you will get #N/A
Avoid this with
=IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),
IF(ISNA(VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE),"Neither"
,VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE))),
VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"terilad" wrote in message
...
Hi is there a way to add multiple VLOOKUP to one cell like below.

=VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) and add this to the cell
as
well VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE)

Many thanks


Mark