If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 atyahoodotcom ---- "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. |
#3
|
|||
|
|||
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 atyahoodotcom ---- "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. |
#4
|
|||
|
|||
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 atyahoodotcom ---- "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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
problem lookup function | bill gras | Worksheet Functions | 2 | June 22nd, 2005 03:57 PM |
How do I use 3 cells to create the string for a lookup function? | Bencomo | Worksheet Functions | 1 | May 15th, 2005 07:17 AM |
Function problem | Miles | General Discussion | 1 | January 20th, 2005 11:06 PM |
Custom Function Problem | Phil Hageman | Worksheet Functions | 4 | July 27th, 2004 06:53 PM |
Matching / lookup problem | Nick | Worksheet Functions | 3 | January 13th, 2004 07:26 AM |