Simple Vlookup Question

[Sergio_Montenegro]

Hi folks, this is probably very simple but I am an Excel novice...

In Cell H5 I have a number which is an average weight

IN column A I have a range of numbers from 0-2000 increases from 0 to 2,000 and represents a single weight unit (A24-A2024)

In column B I have a cost attached to each of of the weight from 0-2,000 (B24-B2024)

What I want to do is in Cell N5 is to populate the cost linked to the individual weight relative in column A

For example, if the average weight in Cell H5 is 275, then I want to enter the relveant cost which relates to 275 (from column B)

I hope this makes sense

Cheers

Serge

 

[Sergio_Montenegro]

There's no reason that shouldn't work. You can even put a formula in for your lookup critiera ( i.e. vlookup((E5/B5),range,col,0) ) What does the vlookup return?

It returns even when I try the formula.. So close...

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl65 id=td_post_2721047 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 96pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 47.25pt; BACKGROUND-COLOR: transparent" width=128 colSpan=2 height=63 rowSpan=3>#N/A

</TD></TR><TR style="HEIGHT: 15.75pt" height=21></TR><TR style="HEIGHT: 15.75pt" height=21></TR></TBODY></TABLE>

 

[Auracle]

Nearly there.... But only 1 minor thing... If I enter a number in H5 then it works... However as H5 is a formula, it is not working... Thanks for your patience...

H5 is actualy the value of E5/B5

I think you have two options, depending on if you need to keep the formula in H5.

1) If you need to keep the formula in H5 (for instance, because you want it to update based on changing values in B5 and E5), then try replacing H5 with VALUE(H5) in the formula and that should then look for the result of the formula;
2) If you don't need to keep the formula, you can copy cell H5 and then do Paste Special Values. In Excel 2007 you can find it in the Ribbon under Paste, open the drop down menu, select Paste Special, and then select Values. Or, if you're like me and like keyboard shortcuts (this will work in Excel 2003 or 2007), you can press Alt, then E, then S, then V.

 

[Asator]

Possible rounding error? In your example, 80 units at 81,000g = 1012.5

Is there an entry for that value in your table?

If not, try round(e5/b5,0) in your lookup criteria.

 

[Sergio_Montenegro]

I think you have two options, depending on if you need to keep the formula in H5.

1) If you need to keep the formula in H5 (for instance, because you want it to update based on changing values in B5 and E5), then try replacing H5 with VALUE(H5) in the formula and that should then look for the result of the formula;
2) If you don't need to keep the formula, you can copy cell H5 and then do Paste Special Values. In Excel 2007 you can find it in the Ribbon under Paste, open the drop down menu, select Paste Special, and then select Values. Or, if you're like me and like keyboard shortcuts (this will work in Excel 2003 or 2007), you can press Alt, then E, then S, then V.

Aye Caramba, we have a result.... Many thanks to you and Auracle for your help and extreme patience.....

Serge

 

[Auracle]

Aye Caramba, we have a result.... Many thanks to you and Auracle for your help and extreme patience.....

Serge

You are very welcome! And hey, I even learned a couple things myself (thanks, Asator!). I didn't even think of the rounding thing...