Looking up value by closest match & determining cell address.

Hello all,

I've been pulling my hair out over this problem recently, I'm struggling to come up with a solution to size a pipe from a calculated value. See the attached sheet for a more clear explanation for this.

What I'm wanting the sheet to do is look at a calculated value, then refer to a table array. Once its found the minimum value greater than the calculated value (ie. the size up) I'd like it to identify what pipe is needed.

In the arbitrary example attached, It needs to see that the value is 273.45 then look for the smallest value above that which in this case is 288.95. From that I'd like it to return the identifier for the NB of the pipe (in this case it's 12") and return the schedule (this time schedule 80).

Alongside the table and below you can see my attempts to make it work:

1. =INDEX(B5:L28, MATCH(N4,C6:L28,-1), MATCH(N4,C6:L28,-1))
2. =ADDRESS(2,MATCH(N4,C6:L28),4,1)
3. =CELL(Address, INDEX(B5:L28, MATCH(N4,C6:L28,-1), MATCH(N4,C6:L28,-1)))

Lastly any ideas about how to approach this?

Hello JBeaucaire,

Thank you for the quick reply!

Thats really close to what I'm after. Instead of the data validation list for the schedule I'm after giving a set value in the same way as the the pipe size - would I be approaching the problem in the right way to adapt the formula being used for that cell?

I.e. =INDEX($B$6:$B$28, MATCH($N$4, OFFSET($B$6:$B$28, , MATCH($N$5, $C$5:$L$5, 0), , ))+1) to suit searching along the columns?

Originally Posted by JBeaucaire

I guess I'm not understanding. For a two-dimensional table, it is standard to indicate an X and Y. You're doing something different?

Apologies for the misunderstanding. The intention is to suggest a suitable pipe schedule for the calculated sized part. So I suppose the best way to explain it, is the value is known and the X and Y are to be determined from that - for example, I'm wanting it to take the calculated value (in this case its 273mm) look at the table of data and find the next size up (minimum value which is greater or equal to the calculated value) which is similar to what a person would do with the pipe size tables. Once its found that to return the pipes nominal bore and the schedule.

Attached is my the mark 1 spreadsheet which is pretty crudely put together (I'm new to using excel!) which half gets the desired results - Although it has a tendency to fail when you return an equal to result (i.e. if you put 598.52 in which is the same as a 24" NB Schedule 5s pipe).

I'm beginning to think I'm asking too much of excel!

Many thanks for the replies.

Mike

Originally Posted by JBeaucaire

In L7 enter this horrible ugly array formula:

=INDEX($B$1:$B$28, SUMPRODUCT(--(($C$6:$I$28-$L$4)=MIN(IF($C$6:$I$28>=$L$4,ABS($C$6:$I$28-$L$4),"")))*ROW($C$6:$I$28))) & """ Pipe"
& ", Schedule " & INDEX($A$5:$I$5, SUMPRODUCT(--(($C$6:$I$28-$L$4)=MIN(IF($C$6:$I$28>=$L$4, ABS($C$6:$I$28-$L$4), "")))*COLUMN($C$6:$I$28)))

...Confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You'll know the array is active when the curly braces { } appear around your formula.


It's formulas like that that make me want to resort to VBA. Wouldn't it be awesome to be able use a custom formula like:

=BestPipe(L4, C5:I28)

....and get the same answer. Anyway, that works.

That's absolutely brilliant! Thank you very much for that,is there anywhere you'd recommend I'd look to learn how to write formula line that?

Many thanks,

Mike