LOOKUP not finding text

[ptm555]

Hi all, first time on and realise that this question has probably been asked countless times before but I haven't found any suitable answers yet...so apologies in advance if this is a repeated question.

I am trying to use LOOKUP to display certain numbers when a range of words are searched. As such, this is the formula I am using:

=LOOKUP(N2,{"Once Only","Per Day","Per Week","Per Month","Per Year"},{1,1,5,20,260})

E.g. If "Once Only" is found a '1' is displayed and so on and so forth..

Problem is, Excel can't seem to find "Per Month" within a range of cells that do contain it, so it never shows the number 20!

Any idea why this could be? I've tried checking for spaces in the 'Per Month' cells but no joy. I'm using Excel 2003 if that makes any difference.

Any help is much appreciated
thanks
Peter

 

[Richard Schollar]

Hi Peter & Welcome to the Board!

LOOKUP expects your results to be sorted ascending which yours aren't ("Per Week" should come after "Per Month" on an alphabetical basis) - hence the problem.

 

[Jonmo1]

Welcome to the board....

Problem is, Excel can't seem to find "Per Month" within a range of cells that do contain it

Does that mean the cell might be "5 times Per Month", not just "Per Month" ??

If so, put wildcards in there

=LOOKUP(N2,{"*Once Only*","*Per Day*","*Per Week*","*Per Month*","*Per Year*"},{1,1,5,20,260})

Hope this helps...


P.S. And what Richard Said....LOL...

 

[ptm555]

thanks guys, RichardSchollars' reply sorted it!
cheers

 

[Yogi Anand]

Hi all, first time on and realise that this question has probably been asked countless times before but I haven't found any suitable answers yet...so apologies in advance if this is a repeated question.

I am trying to use LOOKUP to display certain numbers when a range of words are searched. As such, this is the formula I am using:

=LOOKUP(N2,{"Once Only","Per Day","Per Week","Per Month","Per Year"},{1,1,5,20,260})

E.g. If "Once Only" is found a '1' is displayed and so on and so forth..

Problem is, Excel can't seem to find "Per Month" within a range of cells that do contain it, so it never shows the number 20!

Any idea why this could be? I've tried checking for spaces in the 'Per Month' cells but no joy. I'm using Excel 2003 if that makes any difference.

Any help is much appreciated
thanks
Peter

Hi Peter:

The values in lookup_vector must be placed in ascending order ... so one way would be to use ...
=LOOKUP(N2,{"Once Only","Per Day","Per Month","Per Week","Per Year"},{1,1,20,5,260})

or

you may use VLOOKUP function ...
=VLOOKUP(N2,{"Once Only",1;"Per Day",1;"Per Week",5;"Per Month",20;"Per Year",260},2,0)

I hope this helps.