Excel Range Lookup

Lenovo / 19514tu
October 20, 2009 at 13:47:08
Specs: Microsoft Windows XP Professional, 1.828 GHz / 1014 MB
I am looking to find out if a specific value falls in a range of values on a separate sheet and return a value. For example:

Sheet 1
<TABLE>
<TR><TH><TH>A<TH>B<TH>C
<TR><TH>1<TD>0<TD>10<TD>Low
<TR><TH>2<TD>11<TD>30<TD>Med
<TR><TH>3<TD>31<TD>50<TD>High
</TABLE>
Where A is the lower value in a range, B is the upper value in a range and C is the value of anything falling in that range.

Sheet 2
<TABLE>
<TR><TH><TH>A<TH>B
<TR><TH>1<TD>7<TD>Low
<TR><TH>2<TD>38<TD>High
<TR><TH>3<TD>15<TD>Med
</TABLE>
Where A is the value used to look up and B is returned from Sheet 1

I am not sure if Lookup or Indexing will work, I am thinking it will need some VBA, but not sure.

Sincerely,
albay


See More: Excel Range Lookup

Report •


#1
October 20, 2009 at 13:50:05
Hopefully these sheets will look better than the badly embedded HTML:

Sheet 1
A B C
1 0 10 Low
2 11 30 Med
3 31 50 High

Sheet 2
A B
1 7 Low
2 38 High
3 15 Med


Report •

#2
October 20, 2009 at 14:35:26
Try this in Sheet2:

=VLOOKUP(A1,Sheet1!$A$1:$C$3,3,1)

As per the Excel Help files, the "1" for the range_lookup argument means:

Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned.

P.S. If you use the pre tags above the comments box you can line up the data in your posts like so:

       Sheet 1
     A    B    C 
1    0   10   Low 
2   11   30   Med
3   31   50   High 


Report •

#3
October 20, 2009 at 14:55:58
Hi,

Further to Derbydad03's response:
Add 51 and 'Out of range' to your lookup table and any values above 50 will return an out of range message.

Regards
PS
The 1 at the end of the VLOOKUP function is the same as the logical value TRUE


Report •

Related Solutions

#4
October 20, 2009 at 15:27:50
re: The 1 at the end of the VLOOKUP function is the same as the logical value TRUE

Not so.

The 1 at the end of the VLOOKUP function performs the same function as the logical value TRUE.

They can't be the same...heck, they don't even look alike! ;-)


Report •

#5
October 20, 2009 at 15:56:03
Hi,

Excel Help:
AND Returns TRUE if all of its arguments are TRUE

The following formula:
=AND(1,TRUE)
returns TRUE.

So 1 is true and TRUE is true,

So in the context used, they are the same - nothing to do with 'performing the same function'.

In fact, 1 and TRUE did not perform any function, they were evaluated by the VLOOKUP code.

HTH


Report •

#6
October 20, 2009 at 17:12:46
Humar,

You totally missed my attempt at humor!



Report •

#7
October 20, 2009 at 18:42:14
Hi,

Apologies to albay223 as this has nothing to do with him/her.

... and you missed the point of my response ...

H


Report •

#8
October 21, 2009 at 13:11:10
Thank you for your help and the witty banter!

Report •


Ask Question