Hi,
The syntax of the countif() formula is range,criteria. You have
specified
the criteria and then the range.
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
Roger, every row said "not occupied". Here's my forumla - what am I
doing
wrong?
I think I figured out the "not occupied" is the false part of the
countif.
=IF(COUNTIF(B5,[nhroster.xls]NHroster_Query!A:A),VLOOKUP(B5,[nhroster.xls]NHroster_Query!A:B,2,0),"Not
Occupied")
:
Hi Terry
Try
=IF(COUNTIF(B2,Sheet2!A:A),VLOOKUP(B2,Sheet2!A:B,2,0),"Not Occupied")
--
Regards
Roger Govier
I need something slightly different. Is this the function I should
be
using?
I have a list of all my room numbers in column b2:b26 sheet 1.
I have a list of occupied rooms in column a2:a26 sheet 2 and names
in
column b
(this is actually in a different file but for the sake of example I
put
it
in sheet 2)
Vlookup doesn't know what to do when a room number on sheet 2 is
missing
because it is currently not occupied.
:
The wizards will generally get you waht you need, but do little to
help
your
understanding of how it is they are doing it. Take a look at
VLOOKUP
and
think of it like this:-
The VLOOKUP function essentially takes a value that you specify,
whether
it
be a hardcoded
number/letter/text or a value within a cell reference, and then
goes
and
looks it up in a table.
It will look for that value in the leftmost column of the table,
and
either
find it or the closest
match, and will then return the corresponding value on the same
row,
in
whatever column of that
table that you tell it to:-
Example - With the following table
D E
1 1 0.20
2 10 0.25
3 20 0.30
4 30 0.35
5 40 0.40
and with your value that you are looking up in say cell A1 (and
let's
assume
it is 25 for
example).
You can put a formula in pretty much any other cell, that says,
take
the
value in cell A1 (25), go
and look for it in the lefthand column of the table (D1
5), and
then
when
you have found it (or
the lowest closest number to it), go to the second column (or
whichever
one
you specify if there
are more than 2) and give me the number that it is on the same row
in
that
column.
So, with the formula being =VLOOKUP(A1,D1:E5,2) it will first take
the
value in A1 which is 25,
then go look for it in the leftmost column (D) of your specified
table
(D1:E5), and it will try to
find that number. Now it isn't there, so what it will do is look
for
the
next lower closest
number, which in this case will be 20, and the 2 in the formula
says
to
go
and get the value in
the 2nd column (E) in your table, that is on the same row as the
20.
That
value in this case is
0.3
If you put the value 30 or 31 or 32 etc into A1 now, you will see
the
result
of the formula
change, because now it will either find those numbers or the lower
closest
number (and in each
case there it is 30), and will subsequently return 0.35 as the
corresponding
value.
Just to show you how the 2 really works in that formula, if you
added
one
more column to your
table so that it looked like this:-
D E F
1 1 0.20 0.15
2 10 0.25 0.25
3 20 0.30 0.35
4 30 0.35 0.45
5 40 0.40 0.55
and you actually wanted the value from Col F, then you would
simpl;y
change
the 2 in the formula
to a 3 to signify the third column, eg:-
=VLOOKUP(A1,D1:E5,3)
With the examples already given, 25 in A1 would return 0.35, and
30/31/32
would return 0.45
The one caveat to all of this (When getting the nearest number is
OK)
is
that the data in your
leftmost column must be sorted in ascending order.
There are times when you would only want it to give you a value if
you
had
an exact match on the
number, and in this instance you would simply add a 4th argument of
0
or
FALSE to the formula,
eg:-
=VLOOKUP(A1,D1:E5,3,0)
or
=VLOOKUP(A1,D1:E5,3,FALSE)
In these cases you do not need to have the data in your leftmost
column
sorted.
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
------------------------------Â------------------------------Â----------------
It's easier to beg forgiveness than ask permission
------------------------------Â------------------------------Â----------------
I need the cell A2 on sheet 1 to return a value found in a table I
have
made
on sheet 2 using LOOKUP. The LOOKUP wizard does not really work
in
this
instance. I have titled the column labels Number and Price. There
are
only
2
columns & 4 rows to the table (including the Column Labels). I
want
to
enter
the frequency of attendance in cell A1 on sheet 1 and have the
result
price
from the table appear in cell A2 sheet 1.
Can anyone help with this request. Urgent many thanks.
