Reference Calculations

[Guest]

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?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Hi Again,

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

Thanks,

 

[Guest]

That does the trick. Thank you very much David!

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

 

[Guest]

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.