Finding the next highest and lowest values

[Cobra17]

i'm trying to find the value A in a table.. so i can ultimately find B based on temp and A. I'm not sure how to lookup based on two values, when one of the values (temp) repeats itself in a table.

Eventually there will be multiple tables, not just the HA-1 table, but for now I need to figure out how to get 'B''... which means I need my A's. If there is a way to skip the A's and just get B, i'm all for that too. The High temp isn't working either.

[Limor_OP]

Hi,
You need the results in Column H?

[Cobra17]

the results are the 'hard coded' column

column H are manually entered by me looking at the tables, I want column 'I' to be formulas to get the same results.

[Limor_OP]

H10 = =lookup(h6,$b$7:$b$62)

H11 =index($c$1:$c$62,aggregate(15,6,row($c$7:$c$62)/($b$7:$b$62=h10),1))

H12 =index($c$1:$c$62,aggregate(15,6,row($c$7:$c$62)/($b$7:$b$62=h10),2))

H13 =index($d$1:$d62,aggregate(15,6,row($c$7:$c$62)/($b$7:$b$62=h10),1))

H14 =index($d$1:$d62,aggregate(15,6,row($c$7:$c$62)/($b$7:$b$62=h10),2))

[Cobra17]

I copied the formula's over but ALowLow and ALowHigh don't change when A is changed, and the B's aren't working correct when you go into higher temps.

[Fluff13]

Do you have the dynamic array functions like FILTER?

[Cobra17]

I do have filter

I did get it to work
LowTemp = =INDEX(B7:B62,MATCH(H6,B7:B62,1)-1)
ALowLow = =INDEX(C7:C62,MATCH(1,(I10=B7:B62)*(H7<=C7:C62),0)-1)
ALowHigh = =INDEX(C7:C62,MATCH(1,(I10=B7:B62)*(H7<=C7:C62),0))
BLowLow = =INDEX(D7:D62,MATCH(1,(I10=B7:B62)*(I11=C7:C62),0),0)
BLowHigh =INDEX(D7:D62,MATCH(1,(I10=B7:B62)*(I12=C7:C62),0),0)

then repeat for the high temps.


I'm totally open to a shorter way if FILTER will do it.

[Fluff13]

How about
I10
=XLOOKUP(H6,B7:B62,B7:B62,"",-1)
I11
=XLOOKUP(H7,FILTER(C7:C62,B7:B62=I10),FILTER(C7:C62,B7:B62=I10),"",-1)
I12
=XLOOKUP(H7,FILTER(C7:C62,B7:B62=I10),FILTER(C7:C62,B7:B62=I10),"",1)
I13
=XLOOKUP(I11,FILTER(C7:C62,B7:B62=I10),FILTER(D7:D62,B7:B62=I10),"",1)
I14
=XLOOKUP(I12,FILTER(C7:C62,B7:B62=I10),FILTER(D7:D62,B7:B62=I10),"",1)

[Cobra17]

that worked too. never heard of a XLOOKUP.

Now i need to work on having an indirect lookup to choose the correct table...lol

[Fluff13]

You're welcome & thanks for the feedback.