#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
No idea what you're trying to do. This?
|
#3
|
|||
|
|||
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. |
#4
|
||||
|
||||
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):
|
#5
|
||||
|
||||
Quote:
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. |
#6
|
|||
|
|||
You're right. I'm sorry. I guess I got confused. Thanks for your help. Your solution is working for me.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
Searching for part, BUT NOT ALL, of a word... | MikeWebster2000 | Outlook | 2 | 09-27-2013 06:35 AM |
Searching Multiple PowerPoint & Word Files | BobbyAre | PowerPoint | 14 | 08-12-2012 06:52 AM |
Finding or searching ^ character in word document | shahin3121 | Word | 2 | 03-05-2012 06:16 PM |
Searching for Word Documents | jocaan | Word | 2 | 09-08-2011 11:48 AM |