# 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
ABC
1010Low
21130Med
33150HighWhere 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
AB
17Low
238High
315Med
Where A is the value used to look up and B is returned from Sheet 1I 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

#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.RegardsPSThe 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 TRUENot 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 TRUEThe 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 •