Reference Calculations
 

This question is long winded so forgive me.

I need to develop a function that takes an identifer matches that id with an
id on a subsequent worksheet and returns a value for that id which is then
used in a math function to calculate forecast.

i.e.
Worksheet 1
ID Item Budget Forecasted Amount
3 Oranges $300 ?
1 Apples $32 ?
3 Oranges $212 ?
5 Pears $198 ?

Worksheet 2
ID Item Cost Per
1 Grapes $0.70
2 Berries $0.25
3 Oranges $0.50
4 Bananas $0.55
5 Pears $0.90

So basically the function i need is that if the client on the first line
item has $300 to spend on oranges and oranges cost $0.50 each based off
Worksheet 2 then how many oranges can the client expect?

Reference Calculations
 

Simplified, all of one worksheet

ID Item Budget Forecasted
3 Oranges $300 600
1 Apples $32 46
3 Oranges $212 424
5 Pears $198 220
=C5/VLOOKUP(A5,$A$8:$C$13,3,FALSE)

ID Item Cost Per
1 Grapes $0.70
2 Berries $0.25
3 Oranges $0.50
4 Bananas $0.55
5 Pears $0.90


"DH487" wrote:

This question is long winded so forgive me.

I need to develop a function that takes an identifer matches that id with an
id on a subsequent worksheet and returns a value for that id which is then
used in a math function to calculate forecast.

i.e.
Worksheet 1
ID Item Budget Forecasted Amount
3 Oranges $300 ?
1 Apples $32 ?
3 Oranges $212 ?
5 Pears $198 ?

Worksheet 2
ID Item Cost Per
1 Grapes $0.70
2 Berries $0.25
3 Oranges $0.50
4 Bananas $0.55
5 Pears $0.90

So basically the function i need is that if the client on the first line
item has $300 to spend on oranges and oranges cost $0.50 each based off
Worksheet 2 then how many oranges can the client expect?

Reference Calculations
 

One note: VLOOKUP will only work if the ID is sorted

"David" wrote:

Simplified, all of one worksheet

ID Item Budget Forecasted
3 Oranges $300 600
1 Apples $32 46
3 Oranges $212 424
5 Pears $198 220
=C5/VLOOKUP(A5,$A$8:$C$13,3,FALSE)

ID Item Cost Per
1 Grapes $0.70
2 Berries $0.25
3 Oranges $0.50
4 Bananas $0.55
5 Pears $0.90


"DH487" wrote:

This question is long winded so forgive me.

I need to develop a function that takes an identifer matches that id with an
id on a subsequent worksheet and returns a value for that id which is then
used in a math function to calculate forecast.

i.e.
Worksheet 1
ID Item Budget Forecasted Amount
3 Oranges $300 ?
1 Apples $32 ?
3 Oranges $212 ?
5 Pears $198 ?

Worksheet 2
ID Item Cost Per
1 Grapes $0.70
2 Berries $0.25
3 Oranges $0.50
4 Bananas $0.55
5 Pears $0.90

So basically the function i need is that if the client on the first line
item has $300 to spend on oranges and oranges cost $0.50 each based off
Worksheet 2 then how many oranges can the client expect?

Reference Calculations
 

Hi Again,

What was outlined worked for me. The lookup table is sorted, but the lookup
value does not need to be sorted.

Thanks,

"K Dales" wrote:

One note: VLOOKUP will only work if the ID is sorted

"David" wrote:

Simplified, all of one worksheet

ID Item Budget Forecasted
3 Oranges $300 600
1 Apples $32 46
3 Oranges $212 424
5 Pears $198 220
=C5/VLOOKUP(A5,$A$8:$C$13,3,FALSE)

ID Item Cost Per
1 Grapes $0.70
2 Berries $0.25
3 Oranges $0.50
4 Bananas $0.55
5 Pears $0.90


"DH487" wrote:

This question is long winded so forgive me.

I need to develop a function that takes an identifer matches that id with an
id on a subsequent worksheet and returns a value for that id which is then
used in a math function to calculate forecast.

i.e.
Worksheet 1
ID Item Budget Forecasted Amount
3 Oranges $300 ?
1 Apples $32 ?
3 Oranges $212 ?
5 Pears $198 ?

Worksheet 2
ID Item Cost Per
1 Grapes $0.70
2 Berries $0.25
3 Oranges $0.50
4 Bananas $0.55
5 Pears $0.90

So basically the function i need is that if the client on the first line
item has $300 to spend on oranges and oranges cost $0.50 each based off
Worksheet 2 then how many oranges can the client expect?

Reference Calculations
 

That does the trick. Thank you very much David!

"David" wrote:

Simplified, all of one worksheet

ID Item Budget Forecasted
3 Oranges $300 600
1 Apples $32 46
3 Oranges $212 424
5 Pears $198 220
=C5/VLOOKUP(A5,$A$8:$C$13,3,FALSE)

ID Item Cost Per
1 Grapes $0.70
2 Berries $0.25
3 Oranges $0.50
4 Bananas $0.55
5 Pears $0.90


"DH487" wrote:

This question is long winded so forgive me.

I need to develop a function that takes an identifer matches that id with an
id on a subsequent worksheet and returns a value for that id which is then
used in a math function to calculate forecast.

i.e.
Worksheet 1
ID Item Budget Forecasted Amount
3 Oranges $300 ?
1 Apples $32 ?
3 Oranges $212 ?
5 Pears $198 ?

Worksheet 2
ID Item Cost Per
1 Grapes $0.70
2 Berries $0.25
3 Oranges $0.50
4 Bananas $0.55
5 Pears $0.90

So basically the function i need is that if the client on the first line
item has $300 to spend on oranges and oranges cost $0.50 each based off
Worksheet 2 then how many oranges can the client expect?

Reference Calculations
 

Vlookup only needs to be sorted if you do not supply the 4th argument (false,
the default is true looking for the closest match). If that is left out then
the lookup range absolutely needs to be sorted.

"David" wrote:

Hi Again,

What was outlined worked for me. The lookup table is sorted, but the lookup
value does not need to be sorted.

Thanks,

"K Dales" wrote:

One note: VLOOKUP will only work if the ID is sorted

"David" wrote:

Simplified, all of one worksheet

ID Item Budget Forecasted
3 Oranges $300 600
1 Apples $32 46
3 Oranges $212 424
5 Pears $198 220
=C5/VLOOKUP(A5,$A$8:$C$13,3,FALSE)

ID Item Cost Per
1 Grapes $0.70
2 Berries $0.25
3 Oranges $0.50
4 Bananas $0.55
5 Pears $0.90


"DH487" wrote:

This question is long winded so forgive me.

I need to develop a function that takes an identifer matches that id with an
id on a subsequent worksheet and returns a value for that id which is then
used in a math function to calculate forecast.

i.e.
Worksheet 1
ID Item Budget Forecasted Amount
3 Oranges $300 ?
1 Apples $32 ?
3 Oranges $212 ?
5 Pears $198 ?

Worksheet 2
ID Item Cost Per
1 Grapes $0.70
2 Berries $0.25
3 Oranges $0.50
4 Bananas $0.55
5 Pears $0.90

So basically the function i need is that if the client on the first line
item has $300 to spend on oranges and oranges cost $0.50 each based off
Worksheet 2 then how many oranges can the client expect?