Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-08-2019, 07:21 PM
Guinness Guinness is offline Troubleshooting Searching for a Word in a List Windows 10 Troubleshooting Searching for a Word in a List Office 2019
Novice
Troubleshooting Searching for a Word in a List
 
Join Date: Aug 2019
Location: Deep South
Posts: 16
Guinness is on a distinguished road
Default Troubleshooting Searching for a Word in a List

=LOOKUP(2,1/SEARCH(Bead!A$2:A$94,E4),Bead!A$2:A$94)



Could anyone please help me see why I'm receiving either a #N/A or "Shell" in this workbook instead of "Glass"? I have ~850 records and most are working fine, but there are a few that aren't. Thanks.
Attached Files
File Type: xlsx Shell Errors.xlsx (14.1 KB, 9 views)
Reply With Quote
  #2  
Old 08-09-2019, 08:48 AM
p45cal's Avatar
p45cal p45cal is offline Troubleshooting Searching for a Word in a List Windows 10 Troubleshooting Searching for a Word in a List Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

No idea what you're trying to do. This?
Attached Files
File Type: xlsx msofficeforums43156Shell Errors.xlsx (14.7 KB, 6 views)
Reply With Quote
  #3  
Old 08-09-2019, 08:59 AM
Guinness Guinness is offline Troubleshooting Searching for a Word in a List Windows 10 Troubleshooting Searching for a Word in a List Office 2019
Novice
Troubleshooting Searching for a Word in a List
 
Join Date: Aug 2019
Location: Deep South
Posts: 16
Guinness is on a distinguished road
Default

Well, that certainly solves another issue I was going to face. Thank you!

My question was why that formula didn't return "glass" when "glass" was in the string. Most of the others are returning the correct word based on the list on the "Bead" tab, but I couldn't see any reason why it was returning shell if glass was the first bead type in the string.

I'm attaching the actual table that shows some of the correct results. The two formulas that I'm working with are in columns B & C. I can't get either of them to work, but as they do the same thing, I'm mainly working with the one in column C.

I hope that clears up any confusion.
Attached Files
File Type: xlsx OpenListingsReport08-07-2019_as_table.xlsx (157.8 KB, 6 views)
Reply With Quote
  #4  
Old 08-09-2019, 09:35 AM
p45cal's Avatar
p45cal p45cal is offline Troubleshooting Searching for a Word in a List Windows 10 Troubleshooting Searching for a Word in a List Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

If I use Excel's Help on LOOKUP, ( LOOKUP function - Office Support ) amongst the informations is:
Important: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.


To see what your lookup_vectors are, in the formula bar, select this section of the formula only:
1/SEARCH(Bead!A$2:A$94,E2)
and press F9 on the keyboard. You get this in the formula bar:
=LOOKUP(2,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE! ;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;# VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VA LUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALU E!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;0.00318 471337579618;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALU E!;#VALUE!;#VALUE!;#VALUE!;0.00178571428571429;#VA LUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALU E!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE! ;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;# VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VA LUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALU E!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE! ;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;# VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VA LUE!;#VALUE!;#VALUE!;#VALUE!},Bead!A$2:A$94)


You're looking up 2 amongst a bunch of #VALUE!s and 2 numeric values (0.00318471337579618 & 0.00178571428571429) which aren't in any particular order (not ascending) so as the Important note says: LOOKUP might not return the correct value.


See also the other remarks (my red):
  • If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.
  • If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value.
So don't use LOOKUP.
Reply With Quote
  #5  
Old 08-09-2019, 12:09 PM
NBVC's Avatar
NBVC NBVC is offline Troubleshooting Searching for a Word in a List Windows 10 Troubleshooting Searching for a Word in a List Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

Quote:
Originally Posted by Guinness View Post
My question was why that formula didn't return "glass" when "glass" was in the string. Most of the others are returning the correct word based on the list on the "Bead" tab, but I couldn't see any reason why it was returning shell if glass was the first bead type in the string.

That is why I gave you the alternate formula in your other thread: https://www.msofficeforums.com/excel...ds-string.html


=INDEX(BeadTypes!$A$2:$A$12, MATCH(MIN(IFERROR(SEARCH(BeadTypes!$A$2:$A$12,D2), "x")), IFERROR(SEARCH(BeadTypes!$A$2:$A$12,D2),"x"),0))


this one finds the first occurance in the string. The LOOKUP() formula finds the last.
Reply With Quote
  #6  
Old 08-09-2019, 07:05 PM
Guinness Guinness is offline Troubleshooting Searching for a Word in a List Windows 10 Troubleshooting Searching for a Word in a List Office 2019
Novice
Troubleshooting Searching for a Word in a List
 
Join Date: Aug 2019
Location: Deep South
Posts: 16
Guinness is on a distinguished road
Default

You're right. I'm sorry. I guess I got confused. Thanks for your help. Your solution is working for me.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Troubleshooting Searching for a Word in a List How to return the full string (entire word) when searching with wildcards in word vba scienceguy Word VBA 13 03-23-2019 03:27 AM
Troubleshooting Searching for a Word in a List Searching for part, BUT NOT ALL, of a word... MikeWebster2000 Outlook 2 09-27-2013 06:35 AM
Troubleshooting Searching for a Word in a List Searching Multiple PowerPoint & Word Files BobbyAre PowerPoint 14 08-12-2012 06:52 AM
Troubleshooting Searching for a Word in a List Finding or searching ^ character in word document shahin3121 Word 2 03-05-2012 06:16 PM
Troubleshooting Searching for a Word in a List Searching for Word Documents jocaan Word 2 09-08-2011 11:48 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:12 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft