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.

Try VLOOKUP:

in A2 on Sheet1

=VLOOKUP(A1,Sheet2!A1:B4,2,0)

HTH

"Kaylean" wrote:

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.

Hi,

If your reference value is located in cell A1, if the range to search
is A2:B5 located in sheet1 ...

=VLOOKUP(A1,Sheet1!A2:B5,2)

HTH
Cheers
Carim

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:D5), 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 :-)
------------------------------*------------------------------*----------------





"Kaylean" wrote in message
...
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.

Thanks very much for you reply. I really appreciate your help.
--
Kaylean


"Ken Wright" wrote:

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:D5), 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 :-)
------------------------------Â*------------------------------Â*----------------





"Kaylean" wrote in message
...
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.

You're very welcome :-)

"Kaylean" wrote in message
...
Thanks very much for you reply. I really appreciate your help.
--
Kaylean


"Ken Wright" wrote:

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:D5), 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 :-)
------------------------------*------------------------------*----------------





"Kaylean" wrote in message
...
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.

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.

"Ken Wright" wrote:

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:D5), 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 :-)
------------------------------Â*------------------------------Â*----------------





"Kaylean" wrote in message
...
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.

Hi Terry

Try
=IF(COUNTIF(B2,Sheet2!A:A),VLOOKUP(B2,Sheet2!A:B,2 ,0),"Not Occupied")

--
Regards
Roger Govier

"terryc" wrote in message
...
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.

"Ken Wright" wrote:

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:D5), 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 :-)
------------------------------Â*------------------------------Â*----------------





"Kaylean" wrote in message
...
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.

Roger, thanks for responding. I'll try it tomorrow. With the "not occupied"
in the function, I'm not sure I explained my problem well enough.

Sheet 2 was an export from Access. Since there isn't a person assigned to
the room, the room number doesn't appear in the export list. On sheet 1 all
the room numbers are listed.

"Roger Govier" wrote:

Hi Terry

Try
=IF(COUNTIF(B2,Sheet2!A:A),VLOOKUP(B2,Sheet2!A:B,2 ,0),"Not Occupied")

--
Regards
Roger Govier

"terryc" wrote in message
...
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.

"Ken Wright" wrote:

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:D5), 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 :-)
------------------------------Â*------------------------------Â*----------------





"Kaylean" wrote in message
...
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.

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")


"Roger Govier" wrote:

Hi Terry

Try
=IF(COUNTIF(B2,Sheet2!A:A),VLOOKUP(B2,Sheet2!A:B,2 ,0),"Not Occupied")

--
Regards
Roger Govier

"terryc" wrote in message
...
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.

"Ken Wright" wrote:

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:D5), 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 :-)
------------------------------Â*------------------------------Â*----------------





"Kaylean" wrote in message
...
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.

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

"terryc" wrote in message
...
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")


"Roger Govier" wrote:

Hi Terry

Try
=IF(COUNTIF(B2,Sheet2!A:A),VLOOKUP(B2,Sheet2!A:B,2 ,0),"Not Occupied")

--
Regards
Roger Govier

"terryc" wrote in message
...
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.

"Ken Wright" wrote:

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:D5), 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 :-)
------------------------------Â*------------------------------Â*----------------





"Kaylean" wrote in message
...
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.