# LOOKUP not finding text

#### ptm555

##### New Member
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

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Richard Schollar

##### MrExcel MVP
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

##### MrExcel MVP
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

##### New Member
thanks guys, RichardSchollars' reply sorted it!
cheers

#### Yogi Anand

##### MrExcel MVP
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.

Replies
5
Views
247
Replies
14
Views
394
Replies
22
Views
487
Replies
3
Views
246
Replies
4
Views
180

1,191,683
Messages
5,987,991
Members
440,124
Latest member
dippy_egg

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back