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.
Hello Jorn_H,
It sounds like you could use the INDEX() and MATCH() functions. Read more here:
https://support.office.com/en-us/article/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd
https://support.office.com/en-us/article/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a
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?