Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
Home
#
Search formule, in table searching in 2 rows.

- Home
- :
- Excel
- :
- General Discussion
- :
- Search formule, in table searching in 2 rows.

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2019 07:19 AM

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 advance

Labels:

20 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2019 08:26 AM - edited 10-04-2019 08:27 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2019 08:55 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2019 01:20 PM

Would you mind sharing a sample file of your data so that I can explain the INDEX() and MATCH() functions in further detail?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2019 02:00 PM - edited 10-04-2019 02:01 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2019 02:10 PM

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?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2019 02:22 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2019 02:27 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2019 02:31 PM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2019 02:31 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2019 02:56 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2019 03:04 PM - edited 10-04-2019 03:06 PM

SolutionMaybe 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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2019 03:09 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2019 03:52 PM

@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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2019 11:50 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2019 12:00 AM - edited 10-05-2019 12:19 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2019 03:03 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2019 06:15 AM

Brilliant! Thank you!

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2019 12:44 PM

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

Microsoft Store

Education

Developer