LOOKUP and SEARCH functions to return a value within a string and accounting for blanks

[Trebor76]

Hi all,

In cell A2 I have the following string:

0>5>10>15>20>25>30

In B2:B27 of a tab called Scope the numbers i.e. 0, 1, 2... are listed. I'm using the following formula to match the number...

=LOOKUP(1,0/SEARCH(Scope!$B$2:$B$27,A2),Scope!$B$2:$B$27)

...which works fine if there are no blanks. How can I account for blanks in the formula?

I'm using Office 365 so maybe there's an alternative function like FILTER though I's still like so get my original formula working.

Many thanks,

Robert

 

[Peter_SSs]

which works fine if there are no blanks.

No blanks where?

• Within the A2 string?
• Blank cells in Scope B2:B27?
• Somewhere else?

Can you give us some sample data where the formula is not doing what you want and also tell us the expected result for that data?

 

[Trebor76]

Hi Peter,

Yes - no blanks in the range B2:B277 of the Scope tab. When there is a zero is returned.

Thanks,

Robert

 

[Peter_SSs]

Yes - no blanks in the range B2:B277 of the Scope tab. When there is a zero is returned.

I assume that you meant B27 not B277 but in any case the current formula result depends on where the blanks are in that range. So this would still be useful ..

Can you give us some sample data where the formula is not doing what you want and also tell us the expected result for that data?

 

[Trebor76]

Hi Peter,

Thanks for your reply. I will try and upload later when I'm at home.

This formula is the closest of what I'm after but if there's a blank or no match in Col. B it strangely returns that entry from Col. A (which I want) for each cell the formula is in:

=XLOOKUP(TRUE,ISNUMBER(SEARCH(Scope!$B$2:$B$27,A2)),Scope!$A$2:$A$27,0)

Any advice would be appreciated.

Regards,

Robert

 

[Trebor76]

I have cross-posted this here

 

[Fluff]

I'm using Office 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

From the data on your cross post, how about

=LET(x,XLOOKUP(--TEXTSPLIT(B3,">"),Scope!$B$2:$B$7,Scope!$A$2:$A$7,""),TAKE(FILTER(x,x<>"",""),,1))

 

[Trebor76]

Hi Fluff,

I have updated my account. Thanks.

The formula looks good though is it possible to return a blank if there's no match instead of returning the first item in the list i.e. for 8>7>6>9 your formula returns "Zero".

Many thanks,

Robert

 

[Trebor76]

Actually never mind. All working now

I appreciate your response

 

[Fluff]

Glad we could help & thanks for the feedback.