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

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History