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.
PReagan Thanks for replaying, I Dont know how those two formules would work on such a big scale so can you give more detail please?
Would you mind sharing a sample file of your data so that I can explain the INDEX() and MATCH() functions in further detail?
PReagan So what needs to happen is the first yellow column, Pmax. Needs to be the formule. The value it needs to look at it the column in front, T. With that value U need to look in the table O3;Y60. I dont know if this is possible but if it is you need to see it like this, before the comma, first example -10 is for column O. After the comma is for row 3 so in this case its just 0. If you need to chance any of this do it I just want it to work somehow!
- I am having difficulty discovering the correlation between the T value (ºC) and the Pmax value (Pa). How is it that you determined the values in the yellow column, Pmax, from the previous column, T? Which columns in the O3:Y60 table should each T value direct you to?
PReagan by each tempature there is a value for the saturated water vapor pressure, what stands above the table in Dutch. So in the table you have to top row, row 3, this stands for whats behind the comma in the column T value (ºC). On the Column O is the temperature before the comma so say the temperature is 35,4 then it needs to find 35 in column O and then in row 3 it needs to find 0,4. then where the cross each other it the value for the yellow cell. so in this case 5784. as I said if it isnt possible in this way but it is in some other way its also fine.