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

 

[Auracle]

In Cell N5 then you would want =VLOOKUP(H5,$A$24:$A$2024,2,0)

 

[Sergio_Montenegro]

In Cell N5 then you would want =VLOOKUP(H5,$A$24:$A$2024,2,0)

Thanks but for some reason that is not working

 

[Auracle]

(edit: And just in case you would like a more detailed description of what that's doing, or if that helps... H5 is the cell the vlookup is looking for the value to match up, $A$24:$A$2024 is the range in which it is looking to see if there is a match, 2 references the 2nd column to the right of the range it is looking in (which is Column B), and 0 = FALSE (you can also put "FALSE" instead of the "0" without the quotes in the formula) which gives you an exact match.

The dollar signs in the range "fix" the lookup range in place...that way if you drag and fill your formula in N5 into other cells, the lookup will still look in the same range rather than adjusting the cell references as you copy/drag the formula. You can also fix just a row or column this way by only putting the dollar sign in front of the column letter or the row number (you can use the F4 key to cycle through the various options as well.))

p.s. I tried to edit my earlier message to include that, but apparently it was outside the allowable time window to do so. Ah well.

 

[Auracle]

Thanks but for some reason that is not working

Are you getting an error value of some sort, or is it just showing the formula in the cell and not the result?

(edit: Also, is the value in cell H5 a static value (ie, doesn't change) or is it the result of another formula?)

 

[Sergio_Montenegro]

(edit: And just in case you would like a more detailed description of what that's doing, or if that helps... H5 is the cell the vlookup is looking for the value to match up, $A$24:$A$2024 is the range in which it is looking to see if there is a match, 2 references the 2nd column to the right of the range it is looking in (which is Column B), and 0 = FALSE (you can also put "FALSE" instead of the "0" without the quotes in the formula) which gives you an exact match.

The dollar signs in the range "fix" the lookup range in place...that way if you drag and fill your formula in N5 into other cells, the lookup will still look in the same range rather than adjusting the cell references as you copy/drag the formula. You can also fix just a row or column this way by only putting the dollar sign in front of the column letter or the row number (you can use the F4 key to cycle through the various options as well.))

p.s. I tried to edit my earlier message to include that, but apparently it was outside the allowable time window to do so. Ah well.

Thanks again Auracle but it is still not working (I was not lying when I said I was a novice).. If I change the 2 in the formula to 1 then it returns the weight I am looking for but when I have 2 in the formula it returns <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=xl66 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>#REF!

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

 

[Sergio_Montenegro]

Are you getting an error value of some sort, or is it just showing the formula in the cell and not the result?

(edit: Also, is the value in cell H5 a static value (ie, doesn't change) or is it the result of another formula?)

H5 is the result of another formula.... This is what I am getting The average wight is 81,000/80 (which populates H5). The average cost (N5) is what I am looking for and the total cost is 80 x the average cost...

<TABLE style="WIDTH: 672pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=896 border=0><COLGROUP><COL style="WIDTH: 48pt" span=14 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: windowtext 1pt solid; HEIGHT: 47.25pt; BACKGROUND-COLOR: transparent" width=128 colSpan=2 height=63 rowSpan=3>Total Volume</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl65 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 96pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=128 colSpan=2 rowSpan=3>Total Weight (g)</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl65 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 96pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=128 colSpan=2 rowSpan=3>Average Weight</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl65 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 96pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=128 colSpan=2 rowSpan=3>Total Cost</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl68 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 96pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent" width=128 colSpan=2 rowSpan=3>Average Cost Per Item</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl74 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 47.25pt; BACKGROUND-COLOR: transparent" colSpan=2 height=63 rowSpan=3>80</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl74 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" colSpan=2 rowSpan=3>81,000</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl74 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" colSpan=2 rowSpan=3>1,013</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" colSpan=2 rowSpan=3>#REF!</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl79 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent" colSpan=2 rowSpan=3>#REF!

</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

 

[Auracle]

OMG...stupid me...*FACEPALM* Change the range from $A$24:$A$2024 to $A$24:$B$2024.

I am so sorry....of course it's going to return #REF! cuz it wasn't referenced in the range....ack.

Thanks again Auracle but it is still not working (I was not lying when I said I was a novice).. If I change the 2 in the formula to 1 then it returns the weight I am looking for but when I have 2 in the formula it returns <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=xl66 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>#REF!


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

 

[Sergio_Montenegro]

OMG...stupid me...*FACEPALM* Change the range from $A$24:$A$2024 to $A$24:$B$2024.

I am so sorry....of course it's going to return #REF! cuz it wasn't referenced in the range....ack.

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

 

[Asator]

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?