Extract a value using 4 criterias inside a table

[thamarico]

Hi, I've been using this forum for a quite a while, but I never posted because most of the time, I found what I was searching for through the search engine. Though, this time it seems I'm finally working on something nobody ever asked about. So, here is my problem...

I'm working on a spreadsheet to anticipate the corrosion rates on multiple equipments, based on 4 criterias. At the moment, we type them down one by one and we search for the corresponding value inside the table we have.

			Temperature
CS (carbon steel)	%Sulfur	TAN	[< 50-100]	[>100 - 150]	[>150 - 200]	[>200 - 250]
	<0.3 - 0.6	< 0,3 - 0,6	1	2	4	5
		> 0,6 - 0,9	8	13	14	18
		> 0,9 - 1,2	32	43	54	61
		> 1,2 - 1,5	100	120	135	148

<tbody>
</tbody>

These values aren't the real ones, but it looks like that. So, if I have a piece of equipment made of CS, I look at the specific table for CS (There are about 7 different groups of materials). I consider the %Sulfur, TAN# and the specific temperature. You also have to consider that this table is repeated with different outputs for higher level of %sulfur (>0.6, up to about 3%)

EX - Material = CS, %Sulfur = 0.4, TAN# = 0.7, Temp = 152, my value will be 9 (the bold numbers are the outputs). There is no linear relation or any sort of mathematical way to obtain the output, you have to look at the table.

In my mind, this part of my spreadsheet should look just like that :

Material	%Sulfur	TAN#	Temperature	CORR RATE
W	X	Y	Z	= Output

<tbody>
</tbody>

The excel part now, I tried working with IF() to specify a table to use as the TRUE value, trying to group multiple IF statement into one, but searching values inside multiple ranges is very tedious and I cannot cover all the possibilities... Then I tried working with INDEX() and MATCH() but again, these are not exact values, they are ranges, making the work even harder. Building an output with multiple criterias that are simple and exact is easy, I just can't seem to work it out with ranges.

I must admit that I don't know a lot about VBA, but if it's the only way out, I'll learn how to do it. Though, I'm almost sure that it could be done using simple excel formulas. The worst part is that I'm almost sure that I could do it using MATLAB, but the company I'm working for isn't using it - and - won't buy it haha. Finally, can it be done ? I'm stuck and could use some help, even if it's only where I should be working on, what I should search for.

Thanks !

(I'm sorry if it's hard to read sometimes, english isn't my first language)

 

[thamarico]

It is entirely solved ! - Some wizardry tricks happened and ... no, only plain old fashionned knowledge from someone generous enough to spend some of his time trying to solve a problem, from someone he didn't know.

The last error with the match function was finally nothing too big, only a couple of values in ''text'' format mixed with the correct format. Only thing left to do was to convert them and everything was done.

Again, thanks a lot and I wish you the best !

 

[cyrilbrd]

You are most welcome thamarico.
Glad it worked!