Hi All

can't quite figure out what formula to use
if i have the number 27.568 in a cell
and i have a table in sheet2 with the following structure
CODE......LOW.....HIGH
A.......26.258.......26.358
B.......27.259........27.359
C.......27.566........27.666

how do i lookup the 27.568 and return the C (as it falls within this
range) - additionally, sometimes the value i'm looking up might match either
a LOW or HIGH and then the associated code needs to be returned.

Cheers
JulieD

Hi JulieD,

You can use the VLOOKUP function in Excel to look up a value between two numbers and return a related value. Here's how you can do it:

1. First, make sure that your table in Sheet2 is sorted in ascending order by the LOW column. This is important for the VLOOKUP function to work correctly.
   
2. In the cell where you want to display the code (let's say it's cell A1), enter the following formula:
   
   
   Formula:

   =VLOOKUP(B1,Sheet2!$B$2:$C$4,1,TRUE) 

   Here, B1 is the cell where you have the number 27.568, Sheet2!$B$2:$C$4 is the range of your table (excluding the header row), 1 is the column number of the CODE column in your table, and TRUE specifies that you want an approximate match.
   
3. Press Enter to apply the formula. The formula should return the value "C", which is the code associated with the range that includes 27.568.
   
   If the value you're looking up matches either a LOW or HIGH value in your table, the formula will return the corresponding CODE value. For example, if you were looking up the value 26.358, the formula would return "A".
   

Hi Julie,

In general, it is easier to look up a value that is to the right of the
threshold value. You could introduce an extra column, with just "=A1" as
formula.
Also, you don't really need the MAX column.
Let's assume your Code is in column A, Low in column B, High in column C and
a copy of A in D. The value to be looked up is in E1.
Then the formula would be:

=VLOOKUP(E1,B1:D3,2)

If for some reason you can't insert a new column, use a combination of
MATCH() and INDEX() functions.
Post again if you need help with that.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"JulieD" wrote in message
...
Hi All

can't quite figure out what formula to use
if i have the number 27.568 in a cell
and i have a table in sheet2 with the following structure
CODE......LOW.....HIGH
A.......26.258.......26.358
B.......27.259........27.359
C.......27.566........27.666

how do i lookup the 27.568 and return the C (as it falls within this
range) - additionally, sometimes the value i'm looking up might match
either a LOW or HIGH and then the associated code needs to be returned.

Cheers
JulieD

Oh, and i can't use VLOOKUP as the data in LOW can't be sorted into
ascending order


"JulieD" wrote in message
...
Hi All

can't quite figure out what formula to use
if i have the number 27.568 in a cell
and i have a table in sheet2 with the following structure
CODE......LOW.....HIGH
A.......26.258.......26.358
B.......27.259........27.359
C.......27.566........27.666

how do i lookup the 27.568 and return the C (as it falls within this
range) - additionally, sometimes the value i'm looking up might match
either a LOW or HIGH and then the associated code needs to be returned.

Cheers
JulieD

Hi Julie,

So your example was a bit misleading!
I don't think you'll get your requirement done with standard Excel
functions; you probably need a User Defined Function.
To get that right please tell us what to return if values do not fall within
a range and what if ranges overlap.
Maybe it helps if you tell us what the underlying requirement is.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"JulieD" wrote in message
...
Oh, and i can't use VLOOKUP as the data in LOW can't be sorted into
ascending order


"JulieD" wrote in message
...
Hi All

can't quite figure out what formula to use
if i have the number 27.568 in a cell
and i have a table in sheet2 with the following structure
CODE......LOW.....HIGH
A.......26.258.......26.358
B.......27.259........27.359
C.......27.566........27.666

how do i lookup the 27.568 and return the C (as it falls within this
range) - additionally, sometimes the value i'm looking up might match
either a LOW or HIGH and then the associated code needs to be returned.

Cheers
JulieD

Try the following array formula that needs to entered using
CONTROL+SHIFT+ENTER...

=INDEX(Sheet2!A2:A4,MATCH(1,(Sheet1!A1=Sheet2!B2: B4)*(Sheet1!A1<=Sheet2!C2:C4),0))

...where Sheet1!A1 contains your lookup value.

Hope this helps!

JulieD Wrote:
Hi All

can't quite figure out what formula to use
if i have the number 27.568 in a cell
and i have a table in sheet2 with the following structure
CODE......LOW.....HIGH
A.......26.258.......26.358
B.......27.259........27.359
C.......27.566........27.666

how do i lookup the 27.568 and return the C (as it falls within this
range) - additionally, sometimes the value i'm looking up might match
either
a LOW or HIGH and then the associated code needs to be returned.

Cheers
JulieD


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=276012

if your list is only 3 rows long you can code this with some
if's.....however if your list can be long then a macro is in order


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=276012

Hi Niek

sorry, misleading in that it was in ascending order, but it came with "gaps"
:)

thanks for your input ... its actually a MS Project initated question but i
often think its easier to do the maths in excel than in project. Basically,
the company is using a strange value system to determine priorities of tasks
and they wanted to nominate values for criteria and then return a priority
setting based on these criteria.

after playing around with it a bit more it looks like a vba solution is the
way to go ... there will never be overlapping ranges but there might be gaps
in the ranges and what i guess i was looking for by posting here was to see
if there was some sort of array formula that can be used to lookup between
two columns. (i've not come to grips with what array formulas can & can't be
used for).

i'll play with the vba and post back if i get stuck.

Cheers
JulieD


"Niek Otten" wrote in message
...
Hi Julie,

So your example was a bit misleading!
I don't think you'll get your requirement done with standard Excel
functions; you probably need a User Defined Function.
To get that right please tell us what to return if values do not fall
within a range and what if ranges overlap.
Maybe it helps if you tell us what the underlying requirement is.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"JulieD" wrote in message
...
Oh, and i can't use VLOOKUP as the data in LOW can't be sorted into
ascending order


"JulieD" wrote in message
...
Hi All

can't quite figure out what formula to use
if i have the number 27.568 in a cell
and i have a table in sheet2 with the following structure
CODE......LOW.....HIGH
A.......26.258.......26.358
B.......27.259........27.359
C.......27.566........27.666

how do i lookup the 27.568 and return the C (as it falls within this
range) - additionally, sometimes the value i'm looking up might match
either a LOW or HIGH and then the associated code needs to be returned.

Cheers
JulieD

here is an example for testing 3 rows codes in a7:a9, lows in b7:b9, his
in c7:c9

=IF(AND(B4=B7,B4<=C7),A7,IF(AND(B4=B8,B4<=C8),A8 ,IF(AND(B4=B9,B4<=C9),A9,"N/A")))


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=276012

Hi Domenic

wow looks promising THANKS

Cheers
JulieD

"Domenic" wrote in message
...

Try the following array formula that needs to entered using
CONTROL+SHIFT+ENTER...

=INDEX(Sheet2!A2:A4,MATCH(1,(Sheet1!A1=Sheet2!B2: B4)*(Sheet1!A1<=Sheet2!C2:C4),0))

..where Sheet1!A1 contains your lookup value.

Hope this helps!

JulieD Wrote:
Hi All

can't quite figure out what formula to use
if i have the number 27.568 in a cell
and i have a table in sheet2 with the following structure
CODE......LOW.....HIGH
A.......26.258.......26.358
B.......27.259........27.359
C.......27.566........27.666

how do i lookup the 27.568 and return the C (as it falls within this
range) - additionally, sometimes the value i'm looking up might match
either
a LOW or HIGH and then the associated code needs to be returned.

Cheers
JulieD


--
Domenic
------------------------------------------------------------------------
Domenic's Profile:
http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=276012

Julie,

That table looks a bit odd, there are gaps!.

Assuming this is correct, you might want to amend Domenic's formula to cater
for this

=IF(ISNA(INDEX(A2:A4,MATCH(1,(A1=B2:B4)*(A1<=C2:C 4),0))),"No
match",INDEX(A2:A4,MATCH(1,(A1=B2:B4)*(A1<=C2:C4) ,0)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Domenic" wrote in message
...

Try the following array formula that needs to entered using
CONTROL+SHIFT+ENTER...


=INDEX(Sheet2!A2:A4,MATCH(1,(Sheet1!A1=Sheet2!B2: B4)*(Sheet1!A1<=Sheet2!C2:
C4),0))

..where Sheet1!A1 contains your lookup value.

Hope this helps!

JulieD Wrote:
Hi All

can't quite figure out what formula to use
if i have the number 27.568 in a cell
and i have a table in sheet2 with the following structure
CODE......LOW.....HIGH
A.......26.258.......26.358
B.......27.259........27.359
C.......27.566........27.666

how do i lookup the 27.568 and return the C (as it falls within this
range) - additionally, sometimes the value i'm looking up might match
either
a LOW or HIGH and then the associated code needs to be returned.

Cheers
JulieD


--
Domenic
------------------------------------------------------------------------
Domenic's Profile:
http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=276012

Hi Bob

thanks ...
PS know any good references for learning array formulas?

Cheers
JulieD

"Bob Phillips" wrote in message
...
Julie,

That table looks a bit odd, there are gaps!.

Assuming this is correct, you might want to amend Domenic's formula to
cater
for this

=IF(ISNA(INDEX(A2:A4,MATCH(1,(A1=B2:B4)*(A1<=C2:C 4),0))),"No
match",INDEX(A2:A4,MATCH(1,(A1=B2:B4)*(A1<=C2:C4) ,0)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Domenic" wrote in message
...

Try the following array formula that needs to entered using
CONTROL+SHIFT+ENTER...


=INDEX(Sheet2!A2:A4,MATCH(1,(Sheet1!A1=Sheet2!B2: B4)*(Sheet1!A1<=Sheet2!C2:
C4),0))

..where Sheet1!A1 contains your lookup value.

Hope this helps!

JulieD Wrote:
Hi All

can't quite figure out what formula to use
if i have the number 27.568 in a cell
and i have a table in sheet2 with the following structure
CODE......LOW.....HIGH
A.......26.258.......26.358
B.......27.259........27.359
C.......27.566........27.666

how do i lookup the 27.568 and return the C (as it falls within this
range) - additionally, sometimes the value i'm looking up might match
either
a LOW or HIGH and then the associated code needs to be returned.

Cheers
JulieD


--
Domenic
------------------------------------------------------------------------
Domenic's Profile:
http://www.excelforum.com/member.php...o&userid=10785
View this thread:
http://www.excelforum.com/showthread...hreadid=276012

Hi Julie,

I think that at the level you will be seeking, no I don't. There is Chip's
page of course, http://www.cpearson.com/excel/array.htm, but this is a basic
primer.

The best place to find out is, you guessed it, here in the NGs. Here you
will find such beauties as this posted by Ken Wright yesterday, which draws
out all the dates between 1970 and 1990 where the 1st April is a Saturday

=IF(LARGE((WEEKDAY(DATE(ROW(INDIRECT("1970:1990")) ,4,1),2)=6)*(DATE(ROW(INDI
RECT("1970:1990")),4,1)),ROW(INDIRECT("1:21"))),LA RGE((WEEKDAY(DATE(ROW(INDI
RECT("1970:1990")),4,1),2)=6)*(DATE(ROW(INDIRECT(" 1970:1990")),4,1)),ROW(IND
IRECT("1:21"))),"")

Note the use of ROW as a way of indexing a list - this is very useful in
this type of formula.

This is a array formula that also spans an array, that is a formula where
the target cells are all selected and given the array formula at the same
time. A couple of other nice examples of this can be found at Debra's site
http://www.contextures.com/xlDataVal03.html#ValList. Debra uses these with
DV, but the principle applies. I often use the same principle to produce
lists of unique items

There are also a few examples in a paper on my site,
http://www.xldynamic.com/source/xld.LastValue.html/

I hope that these help, but as ever, trial and error is best.

Bob

PS Domenic's formula is good, does it do what you want?


"JulieD" wrote in message
...
Hi Bob

thanks ...
PS know any good references for learning array formulas?

Cheers
JulieD

"Bob Phillips" wrote in message
...
Julie,

That table looks a bit odd, there are gaps!.

Assuming this is correct, you might want to amend Domenic's formula to
cater
for this

=IF(ISNA(INDEX(A2:A4,MATCH(1,(A1=B2:B4)*(A1<=C2:C 4),0))),"No
match",INDEX(A2:A4,MATCH(1,(A1=B2:B4)*(A1<=C2:C4) ,0)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Domenic" wrote in message
...

Try the following array formula that needs to entered using
CONTROL+SHIFT+ENTER...



=INDEX(Sheet2!A2:A4,MATCH(1,(Sheet1!A1=Sheet2!B2: B4)*(Sheet1!A1<=Sheet2!C2:
C4),0))

..where Sheet1!A1 contains your lookup value.

Hope this helps!

JulieD Wrote:
Hi All

can't quite figure out what formula to use
if i have the number 27.568 in a cell
and i have a table in sheet2 with the following structure
CODE......LOW.....HIGH
A.......26.258.......26.358
B.......27.259........27.359
C.......27.566........27.666

how do i lookup the 27.568 and return the C (as it falls within this
range) - additionally, sometimes the value i'm looking up might match
either
a LOW or HIGH and then the associated code needs to be returned.

Cheers
JulieD


--
Domenic

------------------------------------------------------------------------
Domenic's Profile:
http://www.excelforum.com/member.php...o&userid=10785
View this thread:
http://www.excelforum.com/showthread...hreadid=276012

and one mo

Bob Umlas's white paper:
http://www.emailoffice.com/excel/arrays-bobumlas.html

JulieD wrote:

Hi Bob

thanks ...
PS know any good references for learning array formulas?

Cheers
JulieD

"Bob Phillips" wrote in message
...
Julie,

That table looks a bit odd, there are gaps!.

Assuming this is correct, you might want to amend Domenic's formula to
cater
for this

=IF(ISNA(INDEX(A2:A4,MATCH(1,(A1=B2:B4)*(A1<=C2:C 4),0))),"No
match",INDEX(A2:A4,MATCH(1,(A1=B2:B4)*(A1<=C2:C4) ,0)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Domenic" wrote in message
...

Try the following array formula that needs to entered using
CONTROL+SHIFT+ENTER...


=INDEX(Sheet2!A2:A4,MATCH(1,(Sheet1!A1=Sheet2!B2: B4)*(Sheet1!A1<=Sheet2!C2:
C4),0))

..where Sheet1!A1 contains your lookup value.

Hope this helps!

JulieD Wrote:
Hi All

can't quite figure out what formula to use
if i have the number 27.568 in a cell
and i have a table in sheet2 with the following structure
CODE......LOW.....HIGH
A.......26.258.......26.358
B.......27.259........27.359
C.......27.566........27.666

how do i lookup the 27.568 and return the C (as it falls within this
range) - additionally, sometimes the value i'm looking up might match
either
a LOW or HIGH and then the associated code needs to be returned.

Cheers
JulieD


--
Domenic
------------------------------------------------------------------------
Domenic's Profile:
http://www.excelforum.com/member.php...o&userid=10785
View this thread:
http://www.excelforum.com/showthread...hreadid=276012




--

Dave Peterson

Hi

thanks for the references Bob & Dave i'll check them out when i have a bit
of free time. And yes, thanks, Bob in the 20 test figures, Dominic's
formula worked really well (along with your error handling - saved me some
typing there <vbg!) i will apply it to the "real" data in the next couple
of days and see how that turns out.

Cheers
JulieD



"Bob Phillips" wrote in message
...
Hi Julie,

I think that at the level you will be seeking, no I don't. There is Chip's
page of course, http://www.cpearson.com/excel/array.htm, but this is a
basic
primer.

The best place to find out is, you guessed it, here in the NGs. Here you
will find such beauties as this posted by Ken Wright yesterday, which
draws
out all the dates between 1970 and 1990 where the 1st April is a Saturday

=IF(LARGE((WEEKDAY(DATE(ROW(INDIRECT("1970:1990")) ,4,1),2)=6)*(DATE(ROW(INDI
RECT("1970:1990")),4,1)),ROW(INDIRECT("1:21"))),LA RGE((WEEKDAY(DATE(ROW(INDI
RECT("1970:1990")),4,1),2)=6)*(DATE(ROW(INDIRECT(" 1970:1990")),4,1)),ROW(IND
IRECT("1:21"))),"")

Note the use of ROW as a way of indexing a list - this is very useful in
this type of formula.

This is a array formula that also spans an array, that is a formula where
the target cells are all selected and given the array formula at the same
time. A couple of other nice examples of this can be found at Debra's site
http://www.contextures.com/xlDataVal03.html#ValList. Debra uses these with
DV, but the principle applies. I often use the same principle to produce
lists of unique items

There are also a few examples in a paper on my site,
http://www.xldynamic.com/source/xld.LastValue.html/

I hope that these help, but as ever, trial and error is best.

Bob

PS Domenic's formula is good, does it do what you want?


"JulieD" wrote in message
...
Hi Bob

thanks ...
PS know any good references for learning array formulas?

Cheers
JulieD

"Bob Phillips" wrote in message
...
Julie,

That table looks a bit odd, there are gaps!.

Assuming this is correct, you might want to amend Domenic's formula to
cater
for this

=IF(ISNA(INDEX(A2:A4,MATCH(1,(A1=B2:B4)*(A1<=C2:C 4),0))),"No
match",INDEX(A2:A4,MATCH(1,(A1=B2:B4)*(A1<=C2:C4) ,0)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Domenic" wrote in message
...

Try the following array formula that needs to entered using
CONTROL+SHIFT+ENTER...



=INDEX(Sheet2!A2:A4,MATCH(1,(Sheet1!A1=Sheet2!B2: B4)*(Sheet1!A1<=Sheet2!C2:
C4),0))

..where Sheet1!A1 contains your lookup value.

Hope this helps!

JulieD Wrote:
Hi All

can't quite figure out what formula to use
if i have the number 27.568 in a cell
and i have a table in sheet2 with the following structure
CODE......LOW.....HIGH
A.......26.258.......26.358
B.......27.259........27.359
C.......27.566........27.666

how do i lookup the 27.568 and return the C (as it falls within this
range) - additionally, sometimes the value i'm looking up might
match
either
a LOW or HIGH and then the associated code needs to be returned.

Cheers
JulieD


--
Domenic

------------------------------------------------------------------------
Domenic's Profile:
http://www.excelforum.com/member.php...o&userid=10785
View this thread:
http://www.excelforum.com/showthread...hreadid=276012