Extract a value using 4 criterias inside a table

thamarico

New Member
Joined
Sep 16, 2012
Messages
6
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)%SulfurTAN[< 50-100][>100 - 150][>150 - 200][>200 - 250]
<0.3 - 0.6< 0,3 - 0,61245
> 0,6 - 0,98131418
> 0,9 - 1,232435461
> 1,2 - 1,5100120135148

<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%SulfurTAN#TemperatureCORR RATE
WXYZ= 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)
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi, should the values <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-family:"Calibri \(Body\)"; mso-generic-font-family:auto; mso-font-charset:0;} .xl64 {text-align:center; vertical-align:middle; white-space:normal;} --> </style>
> 0,6 - 0,9

<colgroup><col style="width:65pt" width="65"> </colgroup><tbody>
</tbody>
or <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-family:"Calibri \(Body\)"; mso-generic-font-family:auto; mso-font-charset:0;} .xl64 {vertical-align:middle; white-space:normal;} --> </style>
[>100 - 150]

<colgroup><col style="width:65pt" width="65"> </colgroup><tbody>
</tbody>

remain as they are or is the file flexible for modification?
 
Upvote 0
Hi, thanks for the quick reply !

They could be changed, I only wrote them this way so the people could understand what my values meant. It could become 100 - 150 or anything else, as long as the meaning stays the same. (greater than 100 to exactly or less than 150.. etc)
 
Upvote 0
Ok, two possibilities:
in A1:

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-page-orientation:landscape;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {vertical-align:middle; white-space:normal;} .xl64 {font-weight:700; text-align:center; vertical-align:middle; white-space:normal;} .xl65 {text-align:center; vertical-align:middle; white-space:normal;} --> </style>
CS (carbon steel)%SulfurTAN[>50 - 100][>100 - 150][>150 - 200][>200 - 250]
> 0.0 - 0.6> 0.0 - 0.61245
> 0.6 - 0.98131418
> 0.9 - 1.232435461
> 1.2 - 1.5100120135148

<colgroup><col style="width:65pt" span="7" width="65"> </colgroup><tbody>
</tbody>


In K1:
TANTempCORR RATE
0.712513

<tbody>
</tbody>
With the formula in L2 being:
=INDEX($D$2:$G$5,MATCH(J2,(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($C$2:$C$5,"-",REPT(" ",99)),99)),">",REPT(" ",99)),99))*1)+0.1,1),MATCH(K2,((TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($D$1:$G$1,"-",REPT(" ",99)),99)),"[>",REPT(" ",99)),99))*1)+1),1))
Ctrl + Shift + Enter, not just enter.

Or:
In A1:

CS (carbon steel)%SulfurTAN50101151201
> 0.0 - 0.6> 0.0 - 0.61245
> 0.0 - 0.6> 0.6 - 0.98131418
> 0.0 - 0.6> 0.9 - 1.232435461
> 0.0 - 0.6> 1.2 - 1.5100120135148
> 0.6 - 0.9> 0.0 - 0.624811
> 0.6 - 0.9> 0.6 - 0.917283038
> 0.6 - 0.9> 0.9 - 1.26891114129
> 0.6 - 0.9> 1.2 - 1.5212254286314
> 0.9 - 1.2> 0.0 - 0.6491724
> 0.9 - 1.2> 0.6 - 0.936606481
> 0.9 - 1.2> 0.9 - 1.2146195244276
> 0.9 - 1.2> 1.2 - 1.5454544612672
> 1.2 - 1.5> 0.0 - 0.69193651
> 1.2 - 1.5> 0.6 - 0.977128137173
> 1.2 - 1.5> 0.9 - 1.2312417522591
> 1.2 - 1.5> 1.2 - 1.5972116413101438

<tbody>
</tbody>


In J1:
%SulfurTANTempCORR RATE
0.80.712528

<tbody>
</tbody>

Formula in M2:
=LOOKUP(2,1/(((TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($B$2:$B$17,"-",REPT(" ",99)),99)),">",REPT(" ",99)),99))*1)+0.1<=J2)*((TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($C$2:$C$17,"-",REPT(" ",99)),99)),">",REPT(" ",99)),99))*1)+0.1<=K2)),INDEX($D$2:$G$17,0,MATCH(L2,$D$1:$G$1,1)))
Enter.


Second setup might be more useful for a database containing concentration of S up to 3%.

Is this close to what you needed?
 
Upvote 0
Oh, wow ! Never thought you would provide me with such a magnificient function. I was about to go to bed, but I'll give it a try right away, that's truly art right there. I'll try to give a feedback tonight.

Thanks again !
 
Upvote 0
I just noticed that the table of the first example is off... This aid, I would suggest you to go with the second setup, if you are willing to set the temperature as proposed... Or you could keep the old temp and add a helper row for the formula...

thanks for the feedback.
 
Upvote 0
Well, it seems that my body won't allow me to stay awake any longer.

So, I'll continue my testing tomorrow - though I must say at first that something isn't working, but I can't set my mind to work on it.

Formula in M2:
=LOOKUP(2,1/(((TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($B$2:$B$17,"-",REPT(" ",99)),99)),">",REPT(" ",99)),99))*1)+0.1<=J2)*((TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($C$2:$C$17,"-",REPT(" ",99)),99)),">",REPT(" ",99)),99))*1)+0.1<=K2)),INDEX($D$2:$G$17,0,MATCH(L2,$D$1:$G$1,1)))
Enter.

The : ''+0.1'' is where the error should be, according to Excel. Anyway, have a good night ! I'll be back tomorrow - this must work, it would be too great.
 
Upvote 0
yup you may remove the +0.1, it should work as well.
=LOOKUP(2,1/(((TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($B$2:$B$17,"-",REPT(" ",99)),99)),">",REPT(" ",99)),99))*1)<=J2)*((TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($C$2:$C$17,"-",REPT(" ",99)),99)),">",REPT(" ",99)),99))*1)<=K2)),INDEX($D$2:$G$17,0,MATCH(L2,$D$1:$G$1,1)))
 
Upvote 0
I'm back after a very long day at work ! Finally, I could really try to make it work, but there is still something - which didn't seem too much of a trouble at first - but became one. To be honest, I'm not entirely sure what's happening, but my MATCH() function isn't working when matching cell data.

Example :
MATCH(A1,C1:E1,1) Let's say that cell A1 contains a number and that C1:E1 is my array.

When I try to match this value and obtain its position, excel gives me the #N/A error. The A1 cell is the problem, it's as if it won't recognize what is in the cell. Is it somewhat related to my version of excel ? (excel 2003) I'll continue tomorrow, but everything else seems to be fine - even if this error is stopping me from using the function.
 
Upvote 0
What are your values in C1:E1?
What is your value in A1?
Given In A1:
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-page-orientation:landscape;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} --> </style>
aabc

<colgroup><col style="width:65pt" span="5" width="65"> </colgroup><tbody>
</tbody>

Then the formula you posted should return 1.
please try if that specific example works.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top