index match

[DANWEST87]

=INDEX('Permisible Pressure Drop '!$B$3:$AB$20,MATCH(Certificate!$AG$26,'Permisible Pressure Drop '!$A$3:$A$20,1),MATCH(TRUE,INDEX('Permisible Pressure Drop '!$B$2:$AB$2=Certificate!$Y$46,0),0))Hi Guys,

Hi Guys

I have just come across a problem with the above formula which i can not seem to find the answer to. Basically if my room volume is in-between one of the figures across the top of the table i get #N/A. I want it to round down to the next figure i.e if 33 use 30 And also if my IV(m3) (Left hand side of the table) goes above 1 or bellow 0.15 i also get #n/a. if it goes below .15 I want it to use .15 and if it goes above 1 i want to use 1. I also want to round down when looking at this side of the table i.e if i have .52 entred in AG26 i want it to find and use .5 in the tables left hand column.

I have attached my table so you can get a rough idea of what i am talking about.

Thanks for you help

[martindwilson]

i cannotopen the workbook, but rounding the value looked up may work

[DANWEST87]

mmm not sure why the workbook does not work it has worked before in previous posts. Do you mean round up my figures in the table. If so i cant, it has to be that way for accuracy.
the left hand column is listed .15,.2,.25,.3,.35,.4.,45......so on up to 1.0 and the top row is listed 10,11,12,13,14,15,16, up to 30 then after that goes up in 5's to 60

[FDibbins]

i got an error msg too when trying to open it..."file is in a different format than specified by extention" i managed to open it though

will get back to you

edit: there is only 1 worksheet to the workbook, but your formula indicates at least 1 other also, the sheet name is sheet1, but again, your formula incates otherwise?

=INDEX('Permisible Pressure Drop '!$B$3:$AB$20,MATCH(Certificate!$AG$26,'Permisible Pressure Drop '!$A$3:$A$20,1),MATCH(TRUE,INDEX('Permisible Pressure Drop '!$B$2:$AB$2=Certificate!$Y$46,0),0))

[DANWEST87]

Yes I just attached up the table from my workbook. The sheet you can see is the permissible presure drop sheet and in the original workbook i have another sheet which the certificate relates to. If you need to see the whole workbook I can post it later

[FDibbins]

it will be easier to work with a workbook that more closely resembles what you are working with. that way the formulas developed will make the correct references.

for instance, in your formula above, what are you trying to find, where does it go etc

[DANWEST87]

Hope this attachment works. Here is the full workbook

[FDibbins]

i dont see another attachement? or did you delete the initial attachment and add the new 1?

[DANWEST87]

no i have added it now. Bit of an error on the one I added

[FDibbins]

i get the same error msg, but i was able to open it. will get back to you

[Pete_UK]

You will need to change the final ",0" either to ",1" or delete it entirely. That should round down to the previous value (i.e. 33 to 30).

For the other problem you will need to have an IF to detect if the value is less than 0.15, or you could use MAX(0.15,calculated_value).

Hope this helps.

Pete

[FDibbins]

thats quite some set-up you have there. where exactly is that formula located though?

[DANWEST87]

I did try changing the 0 for a one previously but I was getting strange results that did not correspond to the table. As for the MAX i will have a look at that.

Thanks

[DANWEST87]

Thank you its my first ever spreadsheet I have done with formulas, So its still a bit rough around the edges and it takes me a long time to work everything out. I have been doing it for weeks. The formula is located on the certificate page in cell AK46 labelled permissible pressure drop.

[FDibbins]

i am trying to follow your logic in your file.

in your example, you are trying to find the value from AG26 and Y46?

some minor observations...
sheet1 must have taken you for ever to set up like that, it's really impressive
looking at sheet1 X45, you could simplify that by making (another? lol) table and referencing AG26 to there. and if you dont want to do that, you could just remove the "" from around the numbers "2", "3" etc, that way you can also simplify the formula in AJ44 to =IF(X45>6,X45,6)

[DANWEST87]

Thanks again. Yes I have been thinking about tables a lot recently. Since trying to work out the formula for my current problem I have now discovered using index match with tables. I would of typed the whole thing out in a formula if you were allowed more than 64 arguments in a formula (like the last sheet cell A1 that was my experiment lol) Originally certificate was just a form I printed off and filled in by hand. But i decided to play and make my working day a little bit easier. So long story short, the whole sheet could probably be a lot more simpler if I had know all this in the first place. But oh well Im just going to run with it now. I just really cant understand what I need to add to this formula to make it work.

Thank You

[DANWEST87]

mod edit - duplicate post deleted

[DANWEST87]

[QUOTE=FDibbins;2976724]i am trying to follow your logic in your file.

in your example, you are trying to find the value from AG26 and Y46?

Hi sorry i didnt see that question there.
Yes i am trying to use the figures in those two cells. The figure in AG26 has to be matched to a figure in the left hand column of the table and if in-between figures to use the lower figure i.e round down. And Y46 has to match a figure across the top headings of the table and if in between also round down. (so if 33 use 30 etc).

Thank You

[Pete_UK]

Try this in cell AJ46:

=IF(OR(L23="X",O23="X"),0,IF(S23="X",INDEX('Permisible Pressure Drop '!$B$3:$AB$20,IF($AG$26<'Permisible Pressure Drop '!$A$3,1,MATCH($AG$26,'Permisible Pressure Drop '!$A$3:$A$20)),MATCH(TRUE,INDEX('Permisible Pressure Drop '!$B$2:$AB$2=$Y$46,0)))))

Hope this helps.

Pete

[DANWEST87]

Seems to have the same result I have been getting. If the last match type is set to 0 it will give me the correct figure but only if the room volume entered into cell Y46 is an exact match to one of those in the top row of the table. If i change that to 1 like in your formula, no matter what figure is in the room volume is it will always give me a figure from the last column. (i.e. IV volume is 0.25 room volume is 10m3 it will display 4.0) Basically it is alway saying the room volume is 60m3

Thank you

Dan

[Pete_UK]

Okay, try it like this:

=IF(OR(L23="X",O23="X"),0,IF(S23="X",INDEX('Permisible Pressure Drop '!$B$3:$AB$20,IF($AG$26<'Permisible Pressure Drop '!$A$3,1,MATCH($AG$26,'Permisible Pressure Drop '!$A$3:$A$20)),IF($Y$46<'Permisible Pressure Drop '!$B$2,1,MATCH($Y$46,'Permisible Pressure Drop '!$B$2:$AB$2))))))

Hope this helps.

Pete

[DANWEST87]

Amazing. Cant Thank you enough. Worked perfectly.

[Pete_UK]

Good to hear it. You could use MAX instead of the IF constructions, as I said in post #11. The formula would become:

=IF(OR(L23="X",O23="X"),0,IF(S23<>"X",0,INDEX('Permisible Pressure Drop '!$B$3:$AB$20,MATCH(MAX($AG$26,'Permisible Pressure Drop '!$A$3),'Permisible Pressure Drop '!$A$3:$A$20)),MATCH(MAX($Y$46,'Permisible Pressure Drop '!$B$2),'Permisible Pressure Drop '!$B$2:$AB$2))))

which is slightly shorter.

Hope this helps.

Pete

EDIT: The formula also copes with S23 not being equal to X