# Solved Lookup formula for excel - using text March 12, 2012 at 09:26:54
Specs: Windows XP
 Hi,I am hoping that you might be able to assist me with a lookup formula I am seeking (and failing) to set up. I'm trying to work out postage costs for some packages. I have a column of weights which are formatted as "10.00 kgs", "5.00 kgs", "13.00 kgs" etc. I need to calculate the cost of sending the packages. BUT the costs differ depending on the weight of the item. If it weighs between 0-5kgs then it costs £9.75. if it weighs 6-15kgs then it costs £11 and if 16-30kg it costs £15.I am struggling to come up with a formula which works with the the full text (ie including the term kgs) in the code. I really do not want to have to amend the data so that 10.00 kgs turns into 10.00 unless I absolutely have to!Any help would be gratefully received. See More: Lookup formula for excel - using text

#1 March 12, 2012 at 10:41:25
 It is not clear to me what you are struggling with.If the first column of the lookup_array looks like this:10.00 kgs5.00 kgs13.00 kgsThen any VLOOKUP that starts like this will find those strings:=VLOOKUP("10.00 kgs", …Could it be that you are not using the quotes around your lookup_value argument?Since 10.00 kgs is considered to be text by Excel, it won't know what to with it if you try this:=VLOOKUP(10.00 kgs,…Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2 March 12, 2012 at 13:26:24
 Addendum:If the issue is that you are using a range_lookup argument of 1 (TRUE) so that you can find weights like 2.5 kgs, then consider using a Helper Column - which could be hidden - that only contains the number associated with your weights:With 10.00 kgs in A1, put this in B1 and then hide the column:=(LEFT(A1,FIND(" ",A1)-1))*1This will create a "numerical" 10 which can then be found via a VLOOKUP similar to this:=VLOOKUP(11, B1:C1, 2, 1)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#3
March 13, 2012 at 01:41:19 