Solved Lookup formula for excel - using text

March 12, 2012 at 09:26:54
Specs: Windows XP
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

Report •

March 12, 2012 at 10:41:25
✔ Best Answer
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 kgs

Then 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 •

March 12, 2012 at 13:26:24

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.

Report •

March 13, 2012 at 01:41:19
Thanks for your advice. Problem solved!

Report •

Related Solutions

Ask Question