Lookup function problem (kg)

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 <at>yahoo<dot>com
----
"greencecil" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
> ----
> "greencecil" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <at>yahoo<dot>com
----
"greencecil" <[email protected]> wrote in message
news:[email protected]...
> 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?

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 <at>yahoo<dot>com
----
"greencecil" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
> ----
> "greencecil" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <at>yahoo<dot>com
----
"greencecil" <[email protected]> wrote in message
news:[email protected]...
> 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?

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.

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 <at>yahoo<dot>com
----
"greencecil" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
> ----
> "greencecil" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <at>yahoo<dot>com
----
"greencecil" <[email protected]> wrote in message
news:[email protected]...
> 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?