find values with index, match, match

[white_flag]

Hello I have this
first range name "temperature" with the following numbers (20°C, 25°C, 30°C, 45°C ..etc til 500°C)
second range name "products" with the following names (wood, ceramic, steel, etc ..)
those make an table (landa) with some data inside with landa value: from this table I can extract the
values with the following formula:
=(index(landa;match(E14,products;0);match(E15,temperature;0))
my problems are:
1. wen the temperature is diffrent theh what I have in an range. I like to tell to excel, that if did not match
to find the first bigest value ex: I have temp of 32°C and match to give me value from 45°C.
2. wen I have an the temperature higher then 500°C (bigger than what I have in an range) to give me the
value for the last value from range.

 

[jasonb75]

If I read that correctly, try

=(index(landa;match(E14,products;0);match(E15,temperature)+(E15<=500))

 

[white_flag]

But if the value is 100 for example and I have 100°C in the range, formula will give me all the time the next value that it is in the range

 

[jasonb75]

In that case

=(index(landa;match(E14,products;0);match(E15,temperature)+(countif(temperature,E15)=0)))

 

[white_flag]

thx I puted like this:

=INDEX(landa;MATCH(X17;products;0);IF(COUNTIF(temperature;T17)=0;MATCH(T17;temperature)+(T17<=500);MATCH(T17;temperature;0)))

with IF condition to find the match .. what do you think?

 

[jasonb75]

Without testing it, your version looks right, in hindsight, I think my last suggestion will still return #N/A for temperatures greater than 500.

I'll see if I can come up with anything else, but think that's about as good as it will get with your table as it is.

 

[white_flag]

I like to be more simple, but I think, I will survive with this.

so Jason, thx for your time and for your help

 

[jasonb75]

You will still get error if T17 is below 20°C.

Can you reverse direction of the temperatures and associated data in the table?

(500°C ...45°C, 30°C, 25°C, 20°C)

If this is permissable,

=(index(landa;match(E14,products;0);match(min(E15;500),temperature;-1))

Will give you the correct result every time.

 

[white_flag]

brilliant ..thx, mate