SOLVED

Search formule, in table searching in 2 rows.

Copper Contributor

Hello everyone,

 

I want to make an automatic cell with a formule, surrounded cell with blue. I want it so it reads the cell in front, underlined blue in the table on the right, surrounded with red. the next problem is then its bases on the number in front of the comma, first column and behind the comma top row. Note this is only half of the total table! This is the biggest problem cause it is not a small table.

 

Hopefully someone can help me!

thanks in advanceExplanation.PNG

20 Replies

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

@Jorn_H 

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.

 

@Jorn_H 

The question is - if you have 35.45, value shall be taken from column T, or from column U, or calculated proportionally? 

@Sergei Baklan 

@Jorn_H 

Additionally, per your example, (unless I am mistaken) the intersection of 35 and 0.4 outputs 5752 in the table that I am looking at.

Its should round up to the tenth so if its 45 behind its rounds up to 5 so it should be using column U
You are right yes

@Jorn_H 

And what is the precision for the rounding up? Numbers in you table are results of calculations which shown with 2 digital numbers. For example, 35.4000017 will be shown as 34.40, but if formally round up decimal part it gives 35.5. 

It could be round first to two digits and result rounded up to 1 digit, e.g. 35.4000017 -> 35.40 -> 35.40

best response confirmed by Jorn_H (Copper Contributor)
Solution

@Jorn_H 

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.

@Sergei Baklan 

I would love some suggestions to help condense this formula.

I don't get your question totally so pardon me if I give a wrong answer. In your example you use 35.4000017 it should look it the 2 numbers behind the comma so in this case 40 this means it needs to be 35,4. If it is 35.4501827 it should look again and the 2 numbers behind so 45 so round up to 5

@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

 

@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 The formule works for the positive numbers but it does not work for negative numbers so for the first 4 that are negative it's the #N/B error at the point with ROUNDUP. the other 3 work, so thanks for that already!

 

Edit: After looking around I found out the negative numbers had an other '-' in front so I changed those and it works now! Thank you so much!

@PReagan 

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)

image.png

2) Convert texts for negative temperature to numbers applying custom number format 0;-0;-0 to show "-0"

image.png

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

image.png

@Sergei Baklan 

Brilliant! Thank you!

@Jorn_H 

My pleasure!

1 best response

Accepted Solutions
best response confirmed by Jorn_H (Copper Contributor)
Solution

@Jorn_H 

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.

@Sergei Baklan 

I would love some suggestions to help condense this formula.

View solution in original post