I would like to know which formula should i use in this case:

I have one worksheet with these values:
1st worksheet
Column A B
1,045.00 ‚¬ 1.20
1,050.00 ‚¬ 1.60
1,055.00 ‚¬ 2.00
1,060.00 ‚¬ 2.40
1,065.00 ‚¬ 2.80
1,070.00 ‚¬ 3.20
1,075.00 ‚¬ 3.60
1,080.00 ‚¬ 4.10
1,085.00 ‚¬ 4.50
1,090.00 ‚¬ 4.90
1,095.00 ‚¬ 5.30
1,100.00 ‚¬ 5.70
(each value on column A corresponds to the one in column B)

2nd worksheet
Then, i have a result .
If i have a result between 1045 and <1050, how can i attribute in another
cell 1.20.

Thank you
Jimmy

Assuming your sheet 1 is in cells A2:B13
And your value on sheet2 is in A2
=VLOOKUP(A2,Sheet1!$A$2:$B$13,2,TRUE)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"jimmy" wrote:

I would like to know which formula should i use in this case:

I have one worksheet with these values:
1st worksheet
Column A B
1,045.00 ‚¬ 1.20
1,050.00 ‚¬ 1.60
1,055.00 ‚¬ 2.00
1,060.00 ‚¬ 2.40
1,065.00 ‚¬ 2.80
1,070.00 ‚¬ 3.20
1,075.00 ‚¬ 3.60
1,080.00 ‚¬ 4.10
1,085.00 ‚¬ 4.50
1,090.00 ‚¬ 4.90
1,095.00 ‚¬ 5.30
1,100.00 ‚¬ 5.70
(each value on column A corresponds to the one in column B)

2nd worksheet
Then, i have a result .
If i have a result between 1045 and <1050, how can i attribute in another
cell 1.20.

Thank you
Jimmy

hi john,
thanks a lot for the first answer.
But i think i have to be more specific.

On the sheet 1 i have the cell B12 with a result that can be: 1047, 1052,
1058,...
In the same sheet i have the cell B13 that will return the correspondent
value from the B column on sheet 2.

Example:
*value on cell B12=1047

* which formula i should use that will return 1.20 from sheet 2, column B?
(knowing that the value that appears on B12 sheet 1 will be round down to
the inferior multipe of 5)
I know that for a result of 1047 the returned number will be 1.20;
that for a result of 1052 the returned number will be 1.60;
that for a result of 1058 the returned number will be 2;

but how to represent this in a formula?

"John C" escreveu:

Assuming your sheet 1 is in cells A2:B13
And your value on sheet2 is in A2
=VLOOKUP(A2,Sheet1!$A$2:$B$13,2,TRUE)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"jimmy" wrote:

I would like to know which formula should i use in this case:

I have one worksheet with these values:
1st worksheet
Column A B
1,045.00 ‚¬ 1.20
1,050.00 ‚¬ 1.60
1,055.00 ‚¬ 2.00
1,060.00 ‚¬ 2.40
1,065.00 ‚¬ 2.80
1,070.00 ‚¬ 3.20
1,075.00 ‚¬ 3.60
1,080.00 ‚¬ 4.10
1,085.00 ‚¬ 4.50
1,090.00 ‚¬ 4.90
1,095.00 ‚¬ 5.30
1,100.00 ‚¬ 5.70
(each value on column A corresponds to the one in column B)

2nd worksheet
Then, i have a result .
If i have a result between 1045 and <1050, how can i attribute in another
cell 1.20.

Thank you
Jimmy

The VLOOKUP statement I gave you should work fine. Assuming your currency
amounts on sheet 2 are in column A2:A13 and the numbers you are looking for
(1.20 etc.) are in B2:B13 on sheet 2.
Also, your currency amounts must be sorted from low to high.
So you type in B12 what currency amount you are looking up, then the formula
you type in B13 would be:
=VLOOKUP(B12,Sheet2!$A$2:$B$13,2,TRUE)
The VLOOKUP has four arguments, first, the value you are looking up (B12),
where you are looking it up (Sheet2!$A$2:$B$13), which columm in the table
you are looking it up in (2), and the last argument is TRUE or FALSE. If
False, that means VLOOKUP is looking for an exact match. If True, it is
looking for the value that is closest to your lookup value (without going
over). Note: If your table starts at 1045.00, and your value in B12 is less
than that, it will return an error. If you need more clarification, perhaps
give a better idea of what your range of data to be entered into B12 will be,
etc.
--
** John C **

"jimmy" wrote:

hi john,
thanks a lot for the first answer.
But i think i have to be more specific.

On the sheet 1 i have the cell B12 with a result that can be: 1047, 1052,
1058,...
In the same sheet i have the cell B13 that will return the correspondent
value from the B column on sheet 2.

Example:
*value on cell B12=1047

* which formula i should use that will return 1.20 from sheet 2, column B?
(knowing that the value that appears on B12 sheet 1 will be round down to
the inferior multipe of 5)
I know that for a result of 1047 the returned number will be 1.20;
that for a result of 1052 the returned number will be 1.60;
that for a result of 1058 the returned number will be 2;

but how to represent this in a formula?

"John C" escreveu:

Assuming your sheet 1 is in cells A2:B13
And your value on sheet2 is in A2
=VLOOKUP(A2,Sheet1!$A$2:$B$13,2,TRUE)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"jimmy" wrote:

I would like to know which formula should i use in this case:

I have one worksheet with these values:
1st worksheet
Column A B
1,045.00 ‚¬ 1.20
1,050.00 ‚¬ 1.60
1,055.00 ‚¬ 2.00
1,060.00 ‚¬ 2.40
1,065.00 ‚¬ 2.80
1,070.00 ‚¬ 3.20
1,075.00 ‚¬ 3.60
1,080.00 ‚¬ 4.10
1,085.00 ‚¬ 4.50
1,090.00 ‚¬ 4.90
1,095.00 ‚¬ 5.30
1,100.00 ‚¬ 5.70
(each value on column A corresponds to the one in column B)

2nd worksheet
Then, i have a result .
If i have a result between 1045 and <1050, how can i attribute in another
cell 1.20.

Thank you
Jimmy

Thanks John,

it works.

"John C" escreveu:

The VLOOKUP statement I gave you should work fine. Assuming your currency
amounts on sheet 2 are in column A2:A13 and the numbers you are looking for
(1.20 etc.) are in B2:B13 on sheet 2.
Also, your currency amounts must be sorted from low to high.
So you type in B12 what currency amount you are looking up, then the formula
you type in B13 would be:
=VLOOKUP(B12,Sheet2!$A$2:$B$13,2,TRUE)
The VLOOKUP has four arguments, first, the value you are looking up (B12),
where you are looking it up (Sheet2!$A$2:$B$13), which columm in the table
you are looking it up in (2), and the last argument is TRUE or FALSE. If
False, that means VLOOKUP is looking for an exact match. If True, it is
looking for the value that is closest to your lookup value (without going
over). Note: If your table starts at 1045.00, and your value in B12 is less
than that, it will return an error. If you need more clarification, perhaps
give a better idea of what your range of data to be entered into B12 will be,
etc.
--
** John C **

"jimmy" wrote:

hi john,
thanks a lot for the first answer.
But i think i have to be more specific.

On the sheet 1 i have the cell B12 with a result that can be: 1047, 1052,
1058,...
In the same sheet i have the cell B13 that will return the correspondent
value from the B column on sheet 2.

Example:
*value on cell B12=1047

* which formula i should use that will return 1.20 from sheet 2, column B?
(knowing that the value that appears on B12 sheet 1 will be round down to
the inferior multipe of 5)
I know that for a result of 1047 the returned number will be 1.20;
that for a result of 1052 the returned number will be 1.60;
that for a result of 1058 the returned number will be 2;

but how to represent this in a formula?

"John C" escreveu:

Assuming your sheet 1 is in cells A2:B13
And your value on sheet2 is in A2
=VLOOKUP(A2,Sheet1!$A$2:$B$13,2,TRUE)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"jimmy" wrote:

I would like to know which formula should i use in this case:

I have one worksheet with these values:
1st worksheet
Column A B
1,045.00 ‚¬ 1.20
1,050.00 ‚¬ 1.60
1,055.00 ‚¬ 2.00
1,060.00 ‚¬ 2.40
1,065.00 ‚¬ 2.80
1,070.00 ‚¬ 3.20
1,075.00 ‚¬ 3.60
1,080.00 ‚¬ 4.10
1,085.00 ‚¬ 4.50
1,090.00 ‚¬ 4.90
1,095.00 ‚¬ 5.30
1,100.00 ‚¬ 5.70
(each value on column A corresponds to the one in column B)

2nd worksheet
Then, i have a result .
If i have a result between 1045 and <1050, how can i attribute in another
cell 1.20.

Thank you
Jimmy