|
looking up a value between two numbers and returning a related value
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 |
Answer: looking up a value between two numbers and returning a related value
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:
|
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 |
| All times are GMT +1. The time now is 01:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com