Clarification on lookup command

[K.MUTHUKRISHNAN]

Dear Specialists,

I have a clarification ON LOOKUP COMMAND please –

An extract of the output is enclosed

Current in Amps Cross-Section of Conductor to be selected, Sq.mm

1 10

2 20

3 30

4 40

2 20

LOOKUP(F50,F44:G47)

For 2 Amps, shows 20 Sq.mm cable to be selected.

Same is acceptable.


Current in Amps Cross-Section of Conductor to be selected, Sq.mm

1 10

2 20

3 30

4 40

2.5 20

LOOKUP(F50,F44:G47)

For 2.5, we would like to infer 30 sq.mm cable, so that Factor of Safety is Built -in


In practical cases, we try to adopt factor of safety and for many items. Hence we would like the command to return the higher value.

For example, if we have to size a cable based on the current running in the cable, we would like to select a bigger size of cable so that the cable will not catch fire or get damaged.

However, “lookup” command returns the lower value.

Please indicate how we can make “lookup” command to return the higher value of the second column.

If lookup command will not work, which other command will help may also be indicated.

My other email id is mr.k.muthukrishnan@gmail.com .

Will be greatful to receive your advise please. Warm regards

K.MUTHUKRISHNAN

 

[Faseeh]

Hi K.MUTHUKRISHNAN


Try this formula:

=LOOKUP(IF(D2-INT(D2)<0.5,D2,ROUNDUP(D2,0)),A1:A5,B1:B5)


Your data in A1:B4, and D2 contains the lookup_Value.


OR


Use same formula but try changing your table to:

[pre]0.5	10
1.5	20
2.5	30
3.5	40
4.5	20

[/pre]

I Prefer the formula solution.

Regards,

 

[NARAYANK991]

Hi Faseeh ,


I think a different approach may be required.


The OP has chosen a particular example which is amenable to your approach , but knowing the application I can say that this will not always be the case.


The sizes of cables are standard , and there is no requirement that all integer values be covered ; the standard sizes are given here :


http://myelectrical.com/notes/entryid/166/standard-cable-wire-sizes


Hence , what is required is to always select the next higher size ; thus , there are two sizes 16 sq. mm. and 25 sq. mm. ; when the calculated requirement is 18 sq. mm. , what is needed to be done is to use 25 sq. mm.


The issue is not one of rounding off , even though the example used by the OP may suggest it.


Narayan

 

[Faseeh]

Hi Narayan,


Thanks for pointing me in right direction! Here is the table:

[pre]

0.5	10
0.75	20
1	30
1.5	40
2.5	50
4	60
6	70
10	80
16	90
25	100
35	110
50	120
70	130
95	140
120	150
150	160
185	170
240	180
300	190
400	200
500	210
630	220
800	230
1000	240
..and the formula: 


=IF(D1<=9,0.5,IF(ISNUMBER(MATCH(D1,B1:B24,0))=TRUE,LOOKUP(D1,B1:B24,A1:A24),INDEX(A1:A24,LOOKUP(D1,B1:B24,ROW(A1:A24))+1,0)))

[/pre]
..Looks for an exect match, if found returns the corresponding value, if not returns the next higher size in the table. I assumed data in A1:B24.


Any suggestions?


Regards,

 

[shrivallabha]

We can also use [Array Entered] with Faseeh's Layout:

=MIN(IF(B1:B24>=D1,A1:A24))