Posted to microsoft.public.excel.programming
|
|
VLOOKUP
patrick_molloyATNOSPAMhotmail.com
"terilad" wrote in message
...
Do you have an email address and I can send you the file with an
explanation
of what I looking to do?
Many thanks
Mark
"Patrick Molloy" wrote:
sorry, thats still not clear. remember that we cannot see what you're
looking at
so my interp is a table where the first column is the shift, then the
2nd -
80th represent each day
the first row is headers
the 2nd & following rows start with the shift, then under each day, the
person doing the shift
you need a summary
I have dropped a demo file he
http://cid-b8e56c9a5f311cb7.skydrive...fts/shifts.xls
is this anything like what you need?
"terilad" wrote in message
...
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
|