Lookup Function - Gives Wrong Value

Hello,

Lookup() requires the data in the lookup range to be sorted ascending, and it is not sorted that way in your data. I think you can achieve what you want to do with Vlookup() instead:

=VLOOKUP(C2,G2:H5,2,FALSE)

cheers,

To best describe or illustrate your problem you would be better off attaching a dummy workbook not an image, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

If you can view the attachment, i have the values in ascending order.

Really? Have a closer look. Your values are in this order:

half hour
one hour
one and half hour
two hours

If you select the four cells and sort them via Data - sort, they will be arranged like this:

half hour
one and half hour
one hour
two hours

And that is an alphabetical sort. "one and" comes before "one hour". So, no, your data is not sorted ascending, hence the Lookup() fails. The Vlookup() I suggested above does not depend on sort order if the last parameter is FALSE.

See attached workbook for a working solution of the formula I suggested above. I tend to test solutions before I post them.

The error you get is based on your system using the ; (semicolon) where my system uses the , (comma). This is a difference between regional settings of our respective computers and has nothing to do with the correctness of the formula. If you open the attached workbook, you will see the formula with your regional syntax.

Be aware that when you post on an international forum, other people's system settings may vary from yours, so if you copy a formula from the forum, you may need to replace , (commas) with ; (semicolons) before the formulas work on your machine.

cheers,

Hi saleh_binmasood
you could use
=VLOOKUP(C2,G2:H5,2,FALSE)

I agree you should really be using a VLOOKUP with FALSE here, the above is in fact a reiteration of post # 2 - the very first response.

If you want to use lookup (useful in the right context) - the reason yours fails is that your lookup_vector does not have the correct precedent range.

Please Login or Register  to view this content.

note also - not everyone who uses Excel is male - you might find MR.Teylyn takes exception to that assumption.

Hi all,

@saleh_binmasood, the formula I posted in post #2 is exactly what my friend pike pointed out in post #8

Me in #2
=VLOOKUP(C2,G2:H5,2,FALSE)

Pike in #8
=VLOOKUP(C2,G2:H5,2,FALSE)

I wish you would take as much care in testing and applying the suggestions provided by the members here, as the members here do in constructing them.

Initially, while constructing your workbook, please do not pay any attention to fancy formatting and overlaying text boxes. Concentrate on the actual formulas. Once you're sure your calculations are correct, please delve into the fancy fonts and stuff, but make sure that your overlaying rectangles and text boxes still allow you to enter data. In the file you uploaded last, this was not the case.

And, yes, as DonkeyOte has pointed out, not all members who give advice here are male.

Have a great year 2011.

cheers,