VLOOKUP #VALUE! error

[lpipes]

Hey guys,

I'm working on a project for class and I ran into a problem using the vlookup formula. I'm not very excel savvy, so I apologize if my question has a simple solution or if the problem is not made clear.

The formula I am using is returning a #VALUE! error for 3 cells. The good news is that I think I know why. My lookup value for these 3 cells is smaller than the smallest value in my table table array. Is there any way for me to get a value for these cells?

My lookup values are all dates and times, the first 3 of which are earlier than the first date in the table array. For example, 4/1/93 1:00 pm as the lookup value, and 4/1/93 3:10 pm as the first value in the table array. The formula is returning values for every date after the first 3.

My formula looks like this: =VLOOKUP(A2,$I$2:$J$903,2)

Thanks.

 

[T. Valko]

Hey guys,

I'm working on a project for class and I ran into a problem using the vlookup formula. I'm not very excel savvy, so I apologize if my question has a simple solution or if the problem is not made clear.

The formula I am using is returning a #VALUE! error for 3 cells. The good news is that I think I know why. My lookup value for these 3 cells is smaller than the smallest value in my table table array. Is there any way for me to get a value for these cells?

My lookup values are all dates and times, the first 3 of which are earlier than the first date in the table array. For example, 4/1/93 1:00 pm as the lookup value, and 4/1/93 3:10 pm as the first value in the table array. The formula is returning values for every date after the first 3.

My formula looks like this: =VLOOKUP(A2,$I$2:$J$903,2)

Thanks.

Ok, so what result do you want?

You would need to do something like this...

=IF(A2 < I$2,do_this,VLOOKUP(A2,$I$2:$J$903,2))<?XML:NAMESPACE PREFIX = I$2,do_this,VLOOKUP(A2,$I$2 /><I$2,do_this,VLOOKUP(A2,$I$2:$J$903,2))< p></I$2,do_this,VLOOKUP(A2,$I$2:$J$903,2))<>

 

[lpipes]

I tried that formula but to no avail.

The result I am looking for should be an approximation. There is no exact value in the table which corresponds to my lookups, but perhaps I can try to make an approximation? I tried putting in TRUE (because it is my understanding that this will output an approximate value in the absence of an exact one) at the end of my formula, but excel didn't like that.

 

[T. Valko]

I tried that formula but to no avail.

The result I am looking for should be an approximation. There is no exact value in the table which corresponds to my lookups, but perhaps I can try to make an approximation? I tried putting in TRUE (because it is my understanding that this will output an approximate value in the absence of an exact one) at the end of my formula, but excel didn't like that.

If you use a range_lookup argument of TRUE Excel expects the first column of the table_array to be sorted in ascending order like this:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">25</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">74</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">15</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">52</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">20</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">13</TD></TR></TBODY></TABLE>


When you use TRUE as the range_lookup argument and the data is sorted in ascending order if there isn't an exact match of the lookup_value then the result will be the closest match that is LESS THAN the lookup_value.

For example, based on the posted table...

Lookup_value = 18

=VLOOKUP(18,A1:B5,2) = 52

There isn't an exact match of 18. The closest match that is less than 18 is 15. The value that corresponds to 15 is 52.

If the lookup_value is less than the first value in the lookup_array then the formula will return #N/A.

=VLOOKUP(0,A1:B5,2) = #N/A

When you use TRUE as the range_lookup argument and the data is not sorted in ascending order then the results you get will more than likely be incorrect.

So, try sorting your table_array in ascending order if you want a closest match (that is less than the lookup_value) when there isn't an exact match.