Double (triple?) Index Lookup

[thedr9wningman]

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

 

[Max]

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$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

 

[T. Valko]

Hard to tell where your columns end/begin. Follow this general syntaxarray
entered** :

=INDEX(result_range,MATCH(1,(equip_range="equip_type")*(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.

 

[thedr9wningman]

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

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$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
---

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

 

[Max]

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]

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 are: 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 are: 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

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$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
---

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

 

[Max]

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
---