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.

It is not clear to me what you are struggling with. If the first column of the lookup_array looks like this:

10.00 kgs

5.00 kgs

13.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.

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))*1

This 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.

Thanks for your advice. Problem solved!

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History