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 1I am not sure if Lookup or Indexing will work, I am thinking it will need some VBA, but not sure.

Sincerely,

albay

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

Try this in Sheet2: =VLOOKUP(A1,Sheet1!$A$1:$C$3,3,1)

As per the Excel Help files, the "1" for the

range_lookupargument means:

Range_lookupis 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

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

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 functionas the logical value TRUE.They can't be the same...heck, they don't even look alike! ;-)

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

Humar, You totally missed my attempt at humor!

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

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

H

Thank you for your help and the witty banter!

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History