Lookup function problem (kg)
 

Hi, I have written a function that searches in a list of units, when it finds
the right unit, it chooses the factor associated with that unit for further
use in my calculations. I have used this function many times, without any
problems (the function is in Norwegian, where HVIS = IF and SLÃ….OPP=LOOKUP:
HVIS(SLÃ….OPP('Inputdata'!$J$26;Units!$F$36:$F$41) ='Inputdata'!$J$26;SLÃ….OPP('Inputdata'!$J$26;Unit s!$F$36:$F$41;Units!$G$36:$G$41);0

The unit chosen is in cell J26 in the 'Inputdata' sheet and the function
I've written looks in the sheet 'units!' in cells F36:F41 for the same text.
If it finds it it returns the value (calculation factor I need) in the
corresponding row in the next column (G). This has worked totally fine with
all the units I have tried (e.g. MJ/t, l/t etc.), except when looking for
sets of units beginning with 'kg/'
Is there a reason for this?
is the text 'kg' or 'kg/' unrecognisable by the lookup function?
The units in the list F36:F41 are sorted alphabetically and I have also
asked colleagues to check that I know my alphabet. Apparently I do!

So any tips?

At the moment I'm tempted to just use tonnes as units and make the user type
in 0,5 tonnes instead of 500 kg, but I don't like not understanding why I
can't give them the kg option.

Think this alternative would work:
(but you need to translate into your language, etc ..
I don't know Norwegian, sorry)

=IF(ISNA(MATCH(Inputdata!$J$26,Units!$F$36:$F$41,0 )),0,INDEX(Units!$G$36:$G$
41,MATCH(Inputdata!$J$26,Units!$F$36:$F$41,0)))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"greencecil" wrote in message
...
Hi, I have written a function that searches in a list of units, when it
finds
the right unit, it chooses the factor associated with that unit for
further
use in my calculations. I have used this function many times, without any
problems (the function is in Norwegian, where HVIS = IF and
SLÅ.OPP=LOOKUP:

HVIS(SLÅ.OPP('Inputdata'!$J$26;Units!$F$36:$F$41)= 'Inputdata'!$J$26;SLÅ.OPP(
'Inputdata'!$J$26;Units!$F$36:$F$41;Units!$G$36:$G $41);0

The unit chosen is in cell J26 in the 'Inputdata' sheet and the function
I've written looks in the sheet 'units!' in cells F36:F41 for the same
text.
If it finds it it returns the value (calculation factor I need) in the
corresponding row in the next column (G). This has worked totally fine
with
all the units I have tried (e.g. MJ/t, l/t etc.), except when looking for
sets of units beginning with 'kg/'
Is there a reason for this?
is the text 'kg' or 'kg/' unrecognisable by the lookup function?
The units in the list F36:F41 are sorted alphabetically and I have also
asked colleagues to check that I know my alphabet. Apparently I do!

So any tips?

At the moment I'm tempted to just use tonnes as units and make the user
type
in 0,5 tonnes instead of 500 kg, but I don't like not understanding why I
can't give them the kg option.

Thanks for the suggestion, but I still don't understand why my formula works
for all the other units (text list searched) I try, but not for those
beginning with 'kg/'. If the formula was wrong, it wouldn't work at all would
it?

"Max" wrote:

Think this alternative would work:
(but you need to translate into your language, etc ..
I don't know Norwegian, sorry)

=IF(ISNA(MATCH(Inputdata!$J$26,Units!$F$36:$F$41,0 )),0,INDEX(Units!$G$36:$G$
41,MATCH(Inputdata!$J$26,Units!$F$36:$F$41,0)))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"greencecil" wrote in message
...
Hi, I have written a function that searches in a list of units, when it
finds
the right unit, it chooses the factor associated with that unit for
further
use in my calculations. I have used this function many times, without any
problems (the function is in Norwegian, where HVIS = IF and
SLÃ….OPP=LOOKUP:

HVIS(SLÃ….OPP('Inputdata'!$J$26;Units!$F$36:$F$41) ='Inputdata'!$J$26;SLÃ….OPP(
'Inputdata'!$J$26;Units!$F$36:$F$41;Units!$G$36:$G $41);0

The unit chosen is in cell J26 in the 'Inputdata' sheet and the function
I've written looks in the sheet 'units!' in cells F36:F41 for the same
text.
If it finds it it returns the value (calculation factor I need) in the
corresponding row in the next column (G). This has worked totally fine
with
all the units I have tried (e.g. MJ/t, l/t etc.), except when looking for
sets of units beginning with 'kg/'
Is there a reason for this?
is the text 'kg' or 'kg/' unrecognisable by the lookup function?
The units in the list F36:F41 are sorted alphabetically and I have also
asked colleagues to check that I know my alphabet. Apparently I do!

So any tips?

At the moment I'm tempted to just use tonnes as units and make the user
type
in 0,5 tonnes instead of 500 kg, but I don't like not understanding why I
can't give them the kg option.

I'd hazard a guess that the values in your lookup vector: Units!$F$36:$F$41
are not exactly placed in ascending order ..
So you might have been lucky in getting it to work correctly for some lookup
values, but not for others, as was inferred in your original post. Did the
suggested alternative work for you ?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"greencecil" wrote in message
...
Thanks for the suggestion, but I still don't understand why my formula
works
for all the other units (text list searched) I try, but not for those
beginning with 'kg/'. If the formula was wrong, it wouldn't work at all
would
it?