Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double (triple?) Index Lookup
I struggle with this often, and I've scoured the newsgroups looking for the
answer and I can't find it. I'm trying to look up the Minimum efficiency of an air conditioner unit based on two inputs, the Equipment Type and the Minimum Tons. Whenever I use an index function, I get an error because there are two values in the minimum tons column. I'm trying to find a way to index, for example, a 1-ton unit. Match (with -1, or greater than) doesn't seem to be working. So, let's use an example: Equipment type=Air-cooled AC. Tonnage= 1. The criteria are that the minimum tonnage is inclusive (Tons=value) and the maximum is exclusive (Tons<value). Equipment type index I can do, but I don't know how to exclude the rest of the rows for when the minimum/max tons repeat. 1 is less than 5.4 and greater than 0, so it should be the first row of my table as long as Air-cooled AC is the first column. I should get the answer 10, but I don't know how to do it automatically. Equipment type Minimum efficiency Tons min (inclusive) Tons max Air-cooled AC 10 0.0 5.4 Air-cooled AC 10.3 5.4 11.3 Air-cooled AC 9.7 11.3 20.0 Air-cooled AC 9.5 20.0 63.3 Air-cooled AC 9.2 63.3 [infinity] Water-cooled AC 12.1 0.0 5.4 Water-cooled AC 11.5 5.4 11.3 Water-cooled AC 11 11.3 20.0 Water-cooled AC 11 20.0 63.3 -- Colourless Green Ideas transparency | ecology | economy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double (triple?) Index Lookup
One way
Assume reference table as posted in cols A to D, data from row2 down Inputs in F2: Air-cooled AC (Equipment type) in G2: 1 (Tons) Put in H2, normal ENTER: =INDEX(B$2:B$100,MATCH(1,INDEX((A$2:A$100=F2)*(G2 =C$2:C$100)*(G2<D$2:D$100),),0)) to return the Min Efficiency. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "thedr9wningman" wrote: I struggle with this often, and I've scoured the newsgroups looking for the answer and I can't find it. I'm trying to look up the Minimum efficiency of an air conditioner unit based on two inputs, the Equipment Type and the Minimum Tons. Whenever I use an index function, I get an error because there are two values in the minimum tons column. I'm trying to find a way to index, for example, a 1-ton unit. Match (with -1, or greater than) doesn't seem to be working. So, let's use an example: Equipment type=Air-cooled AC. Tonnage= 1. The criteria are that the minimum tonnage is inclusive (Tons=value) and the maximum is exclusive (Tons<value). Equipment type index I can do, but I don't know how to exclude the rest of the rows for when the minimum/max tons repeat. 1 is less than 5.4 and greater than 0, so it should be the first row of my table as long as Air-cooled AC is the first column. I should get the answer 10, but I don't know how to do it automatically. Equipment type Minimum efficiency Tons min (inclusive) Tons max Air-cooled AC 10 0.0 5.4 Air-cooled AC 10.3 5.4 11.3 Air-cooled AC 9.7 11.3 20.0 Air-cooled AC 9.5 20.0 63.3 Air-cooled AC 9.2 63.3 [infinity] Water-cooled AC 12.1 0.0 5.4 Water-cooled AC 11.5 5.4 11.3 Water-cooled AC 11 11.3 20.0 Water-cooled AC 11 20.0 63.3 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double (triple?) Index Lookup
Hard to tell where your columns end/begin. Follow this general syntaxarray
entered** : =INDEX(result_range,MATCH(1,(equip_range="equip_ty pe")*(min_range<=variable)*(max_range=variable),0 )) Better if you use cells to hold all the criteria: =INDEX(result_range,MATCH(1,(equip_range=A1)*(min_ range<=B1)*(max_range=B1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "thedr9wningman" wrote in message ... I struggle with this often, and I've scoured the newsgroups looking for the answer and I can't find it. I'm trying to look up the Minimum efficiency of an air conditioner unit based on two inputs, the Equipment Type and the Minimum Tons. Whenever I use an index function, I get an error because there are two values in the minimum tons column. I'm trying to find a way to index, for example, a 1-ton unit. Match (with -1, or greater than) doesn't seem to be working. So, let's use an example: Equipment type=Air-cooled AC. Tonnage= 1. The criteria are that the minimum tonnage is inclusive (Tons=value) and the maximum is exclusive (Tons<value). Equipment type index I can do, but I don't know how to exclude the rest of the rows for when the minimum/max tons repeat. 1 is less than 5.4 and greater than 0, so it should be the first row of my table as long as Air-cooled AC is the first column. I should get the answer 10, but I don't know how to do it automatically. Equipment type Minimum efficiency Tons min (inclusive) Tons max Air-cooled AC 10 0.0 5.4 Air-cooled AC 10.3 5.4 11.3 Air-cooled AC 9.7 11.3 20.0 Air-cooled AC 9.5 20.0 63.3 Air-cooled AC 9.2 63.3 [infinity] Water-cooled AC 12.1 0.0 5.4 Water-cooled AC 11.5 5.4 11.3 Water-cooled AC 11 11.3 20.0 Water-cooled AC 11 20.0 63.3 -- Colourless Green Ideas transparency | ecology | economy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double (triple?) Index Lookup
Splendid. So, now I have one more question.
I'm the kind of person who likes to understand what is happening so I don't have to keep coming here asking for more answers, so can someone explain to me the logic behind the Match(1....) part? ....(I'm still amazed at the power of the index function). -- Colourless Green Ideas transparency | ecology | economy "Max" wrote: One way Assume reference table as posted in cols A to D, data from row2 down Inputs in F2: Air-cooled AC (Equipment type) in G2: 1 (Tons) Put in H2, normal ENTER: =INDEX(B$2:B$100,MATCH(1,INDEX((A$2:A$100=F2)*(G2 =C$2:C$100)*(G2<D$2:D$100),),0)) to return the Min Efficiency. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "thedr9wningman" wrote: I struggle with this often, and I've scoured the newsgroups looking for the answer and I can't find it. I'm trying to look up the Minimum efficiency of an air conditioner unit based on two inputs, the Equipment Type and the Minimum Tons. Whenever I use an index function, I get an error because there are two values in the minimum tons column. I'm trying to find a way to index, for example, a 1-ton unit. Match (with -1, or greater than) doesn't seem to be working. So, let's use an example: Equipment type=Air-cooled AC. Tonnage= 1. The criteria are that the minimum tonnage is inclusive (Tons=value) and the maximum is exclusive (Tons<value). Equipment type index I can do, but I don't know how to exclude the rest of the rows for when the minimum/max tons repeat. 1 is less than 5.4 and greater than 0, so it should be the first row of my table as long as Air-cooled AC is the first column. I should get the answer 10, but I don't know how to do it automatically. Equipment type Minimum efficiency Tons min (inclusive) Tons max Air-cooled AC 10 0.0 5.4 Air-cooled AC 10.3 5.4 11.3 Air-cooled AC 9.7 11.3 20.0 Air-cooled AC 9.5 20.0 63.3 Air-cooled AC 9.2 63.3 [infinity] Water-cooled AC 12.1 0.0 5.4 Water-cooled AC 11.5 5.4 11.3 Water-cooled AC 11 11.3 20.0 Water-cooled AC 11 20.0 63.3 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double (triple?) Index Lookup
The MATCH part of it basically reduces to, indicatively:
MATCH(1,{0;0;1;0;0;0,...},0) where the resultant array: {0;0;1;0;0;0,...} arises from the conditions checked: (Cond1)*(Cond2)*(Cond3) with 1's within the array indicating where the multiple conditions are simultaneously satisfied, zeros otherwise MATCH(1,{0;0;1;0;0;0,...},0) returns the relative position of the "1" within the array: {0;0;1;0;0;0,...} ie the position number: 3 The index/match expression hence reduces to, ultimately: INDEX(B$2:B$100,3) which returns the 3rd element in B2:B100, ie it'll return what's in B4 -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "thedr9wningman" wrote: Splendid. So, now I have one more question. I'm the kind of person who likes to understand what is happening so I don't have to keep coming here asking for more answers, so can someone explain to me the logic behind the Match(1....) part? ...(I'm still amazed at the power of the index function). |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double (triple?) Index Lookup
Alright, now I'm using that function in a different context and it is
breaking at the MATCH. Check this out: {=INDEX($C$3:$C$26,MATCH(1,($A$3:$A$26=Location)*1 ,0),1)} this works, but if I take out the *1, it doesn't work. Additionally, {=INDEX(C3:C14,MATCH(1,B3:B14=4*1,0),1)} that doesn't work at all. The parameters I'm using a Location=[Las Vegas, Reno], and currently Las Vegas is selected. In the B column, I have months numbered from 1-12. My ultimate goal, of course is to glue these together, but that doesn't work either: {=INDEX($C$3:$V$26,MATCH(1,($A$3:$A$26=Location)*! $B$3:$B$26='Report Table'!$C$40),0),MATCH(1,($B$1:$V$1=CDD_Basis)*($C $2:$V$2='Lighting Report Table'!D$39),0))} Basically, a double double-index, where I have 4 tables of varying bases all together in one spot. The indeces a Rows: Location and month; columns: CDD basis and whatever is on the report table heading, which matches exactly. When I look at the functions, though, I see the true in the Match functions, but then it gives me a N/A error (I'd get a value if I didn't ctrl-shift-enter). When I evaluate it, it barfs on the Match. I don't understand how it works sometimes and not other times when I'm doing the same thing. I've had good luck with adding the matches and subtracting anything over 1 index, but that seems messy. -- Colourless Green Ideas transparency | ecology | economy "Max" wrote: One way Assume reference table as posted in cols A to D, data from row2 down Inputs in F2: Air-cooled AC (Equipment type) in G2: 1 (Tons) Put in H2, normal ENTER: =INDEX(B$2:B$100,MATCH(1,INDEX((A$2:A$100=F2)*(G2 =C$2:C$100)*(G2<D$2:D$100),),0)) to return the Min Efficiency. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "thedr9wningman" wrote: I struggle with this often, and I've scoured the newsgroups looking for the answer and I can't find it. I'm trying to look up the Minimum efficiency of an air conditioner unit based on two inputs, the Equipment Type and the Minimum Tons. Whenever I use an index function, I get an error because there are two values in the minimum tons column. I'm trying to find a way to index, for example, a 1-ton unit. Match (with -1, or greater than) doesn't seem to be working. So, let's use an example: Equipment type=Air-cooled AC. Tonnage= 1. The criteria are that the minimum tonnage is inclusive (Tons=value) and the maximum is exclusive (Tons<value). Equipment type index I can do, but I don't know how to exclude the rest of the rows for when the minimum/max tons repeat. 1 is less than 5.4 and greater than 0, so it should be the first row of my table as long as Air-cooled AC is the first column. I should get the answer 10, but I don't know how to do it automatically. Equipment type Minimum efficiency Tons min (inclusive) Tons max Air-cooled AC 10 0.0 5.4 Air-cooled AC 10.3 5.4 11.3 Air-cooled AC 9.7 11.3 20.0 Air-cooled AC 9.5 20.0 63.3 Air-cooled AC 9.2 63.3 [infinity] Water-cooled AC 12.1 0.0 5.4 Water-cooled AC 11.5 5.4 11.3 Water-cooled AC 11 11.3 20.0 Water-cooled AC 11 20.0 63.3 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double (triple?) Index Lookup
Pl start new threads for new queries ...
Maximises exposure for your queries to all responders Better for archiving and future referencing P/s: Pl note that my suggestion in this thread doesn't require array-entry. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Double Vertical Index & Match | Excel Worksheet Functions | |||
Variable Lookup/Double Lookup | Excel Worksheet Functions | |||
Triple lookup? | Excel Worksheet Functions | |||
double lookup | Excel Worksheet Functions | |||
dynamic, double vlookup, match, index, dget?? different workbooks | Excel Worksheet Functions |