Forum Discussion
Search formule, in table searching in 2 rows.
Maybe this is what you're after. Try this rather lengthy formula out and let me know if it gives you your desired results:
In Cell G4:
=INDEX($P$4:$Y$60,MATCH(IF(ABS(ROUND($F4-ROUNDDOWN($F4,),1))=1,ROUNDUP($F4,),ROUNDDOWN($F4,)),$O$4:$O$60,0),MATCH(IF(ABS(ROUND($F4-ROUNDDOWN($F4,),1))=1,0,ABS(ROUND($F4-ROUNDDOWN($F4,),1))),$P$3:$Y$3,0))
P.S.
I would love some suggestions to help condense this formula.
Maybe this is what you're after. Try this rather lengthy formula out and let me know if it gives you your desired results:
In Cell G4:
=INDEX($P$4:$Y$60,MATCH(IF(ABS(ROUND($F4-ROUNDDOWN($F4,),1))=1,ROUNDUP($F4,),ROUNDDOWN($F4,)),$O$4:$O$60,0),MATCH(IF(ABS(ROUND($F4-ROUNDDOWN($F4,),1))=1,0,ABS(ROUND($F4-ROUNDDOWN($F4,),1))),$P$3:$Y$3,0))
P.S.
I would love some suggestions to help condense this formula.
Thank you, but I did one mistake. At least. The point is that when we go from zero degreeC up to water or down to ice vapor pressure is different
MATCH find first in sequence row with zero. Thus if we have temperature below the zero but higher than -1, we have to jump on next from found row.
Thus I updated the formula
=INDEX($P$4:$Z$60,MATCH(TRUNC(F5),$O$4:$O$60,0)+0.5*(1-SIGN(F5))*(TRUNC(F5)=0),(10*CEILING(ABS(ROUND(F5-TRUNC(F5),2)),0.1)+1))by adding to MATCH
+0.5*(1-SIGN(F5))*(TRUNC(F5)=0)It becomes longer, but I hope more correct. Result is
Attached file is corrected
Brilliant! Thank you!
Bit shorter formula could be if
1) Add helper column to the vapour pressure table for 1.0 (copy-pasting column for 0 with shifting on one row)
2) Convert texts for negative temperature to numbers applying custom number format 0;-0;-0 to show "-0"
3) Skip MATCH on columns. 0; 0.1; 0.2;... multiplied on 10 and with adding 1 already give us number of the column in the table
Finally it could be like
=INDEX($P$4:$Z$60,MATCH(TRUNC(F4),$O$4:$O$60,0),(10*CEILING(ABS(ROUND(F4-TRUNC(F4),2)),0.1)+1))I keep your formula results in green
PReagan Thanks for helping me, unfortunately it doesn't work I get an error at the first IF formule (ALS in Dutch). It happens at ROUNDUP and it give the error code #N/B. see also the photo's I attached. So down here is the formule rewritten in Dutch. The names are correct, maybe I miss a ) or a ; . Hopefully you can help because it looks promising!
=INDEX($P$4:$Y$60,VERGELIJKEN(ALS(ABS(AFRONDEN($F4-AFRONDEN.NAAR.BENEDEN($F4,),1))=1,AFRONDEN.NAAR.BOVEN($F4,),AFRONDEN.NAAR.BENEDEN($F4,)),$O$4:$O$60,0),VERGELIJKEN(ALS(ABS(AFRONDEN($F4-AFRONDEN.NAAR.BENEDEN($F4,),1))=1,0,ABS(AFRONDEN($F4-AFRONDEN.NAAR.BENEDEN($F4,),1))),$P$3:$Y$3,0))
PReagan Thanks for this formule but unfortunatly it does not work. I tried to see the evaluation from the formule but I dont know why he doesn't want to work. so can you please take a look at it if I missed something?
Note: I needed to rewrite the formule towards my language but I can assure that is right so far.
=INDEX($P$4:$Y$60,VERGELIJKEN(ALS(ABS(AFRONDEN($F4-AFRONDEN.NAAR.BENEDEN($F4,),1))=1,AFRONDEN.NAAR.BOVEN($F4,),AFRONDEN.NAAR.BENEDEN($F4,)),$O$4:$O$60,0),VERGELIJKEN(ALS(ABS(AFRONDEN($F4-AFRONDEN.NAAR.BENEDEN($F4,),1))=1;0;ABS(AFRONDEN($F4-AFRONDEN.NAAR.BENEDEN($F4,),1))),$P$3:$Y$3,0))
It gives the error #N/B. I found out it give it when a formule with MATCH can't find the value it's looking for, see picture. After this stap it's goes to #N/B on the position where it says AFRONDEN.NAAR.BOVEN, means ROUNDUP. So maybe there is a error in the formule
